## 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,

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

Thanks. We'll let you know when a new response is added.
• 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.
report
• 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.
report
• Also I need a formula that can replace the last c with an A in a separate column

report
• Thank you for all your help so far :)
report
• 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.

report
• ```CHD452518C CHD452518 CHD452518A CHD63543C CHD63543 CHD63543A DG686482C DG686482 DG686482A SG3451355255C SG3451355255 SG3451355255A```
report
• 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"

report
• It is an on-going spread sheet
report
• (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")

report
• opps--column C should begin wiht a single =, not a double =
report
• 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'?