Conditional Substitute Formula in Microsoft Excel 2007

75 pts.
Tags:
Excel 2007
In the A column of my spread sheet I have the two following numbers: BH15246C and CKI26632C. I need to find 1 formula that can cause the data to change to the following in the B column: BH15246 and CK126632. In essence I need a formula to copy BH15246C or CKI26632C without the C on the end. Please help!


Software/Hardware used:
Excel 2007,
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Option 1

=if(left(A1,1)=”B”,left(A1,7),left(A1,8))

Option 2

in column C

=len(trim(A1))

in column B

=left(A1,C1-1)

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Is it always a C and is the C always in the same string position? Is it always the last position? If it's a floating position? You may have to do some scan and/or replace. Provide a little more info if this is the case and the suggestion in the answer above does not work for you.
    132,760 pointsBadges:
    report
  • AnnaGe
    It is always at the end (but sometimes it is at the beginning too which is what is causing me a problem) and the numbers vary in length.
    75 pointsBadges:
    report
  • AnnaGe
    Also I need a formula that can replace the last c with an A in a separate column

    75 pointsBadges:
    report
  • AnnaGe
    Thank you for all your help so far :)
    75 pointsBadges:
    report
  • azohawk

    Sounds like this could be a complicated scenario. It may take a couple of hidden columns to extract some selected data, and/or a complex nested if statement combined with Find/ Search/ Replace/ concatinate/ "substring"(mid/left/right)/ substitute functions to get what you need. A little more sampling of data and what you need to look like would be helpful.  Is this is a situaiton if the alpha is lower case you need to do something with it?

    Will this be an ongoing need, or just once? Amount of data (20 entries, 1000? A single case of a handful of data, sometimes it is easier to just maniuplate the data manually. 

    4,055 pointsBadges:
    report
  • AnnaGe
    CHD452518C CHD452518 CHD452518A CHD63543C CHD63543 CHD63543A DG686482C DG686482 DG686482A SG3451355255C SG3451355255 SG3451355255A
    75 pointsBadges:
    report
  • AnnaGe

    so it needs to get rid of the data in Column A's last "C" (for column B)

    And I need another formulae that adds an "A" to the data in column B, or replaces the "C" in column A with an "A"

    75 pointsBadges:
    report
  • AnnaGe
    It is an on-going spread sheet
    75 pointsBadges:
    report
  • azohawk

    (in a hidden column-I'll refer to is column D)

    =find("C", A1, 3)  ..you are looking for the postion of 'C' in the cell A1 after the 3rd postion..

    Column B

    =replace(A1,D1,1," ")

    Column C

    ==replace(A1,D1,1,"A")

    4,055 pointsBadges:
    report
  • azohawk
    opps--column C should begin wiht a single =, not a double =
    4,055 pointsBadges:
    report
  • TheRealRaven
    Are you always dropping a 'C'? Or are you dropping the last (or first) character?

    ...but sometimes it is at the beginning too...

    For
    CKI26632C, how do you know not to drop the initial 'C'?
    35,090 pointsBadges:
    report
  • AnnaGe
    I only want to drop the final c and that is what is causing me problems... Is there no way to create a conditional substitute formula
    75 pointsBadges:
    report
  • azohawk
    All of scenarios that you have given show the issue to be removing/changing the C at the end of data. You might be able to embed some of these commands together, but I would use them the way presented here first to make sure that they work as presented rather than get fancy and  have problems you can't figure out.  Other than the length of the the data, what other conditions are you considering, I don't see them in what you have presented to us.
    4,055 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: