Excel 2007 : Need help separating numbers/text

Hello,

Need help separating the following numbers/text on the same column:

K103-1X

B107X

What I need to do is to separate the X from the rest of the number. Appreciate all your help. Please note, I am not familiar with Excel formulas at all. I tried using the Text-to-Column feature to do this but did not work.

Thank you!

dancediva,

Is the X always the last letter? And do you just need it removed?

~tigeravatar

Hi tigeravatar,

Thank you for replying.

Yes, the X is always the last letter and I need it moved to the column to the right.

dancediva,

Alright, then I'd recommend this:
Insert two columns next to the column that has the data
In this example, the column holding the data is in column A, and the two inserted columns are columns B and C. Also, the data starts in A1
In the cell next to the data (in this example, B1) put in the following formula:

=LEFT(A1,LEN(A1)-1)

Then in the cell to the right of that (in this example, C1) put in the following formula:

=RIGHT(A1,1)

Then copy/paste down until it has done so for all data
Then hide column A

Hope this helps,
~tigeravatar

tigeravatar,

The formula does work. However, there is a problem because not all numbers have the X at the end and the formula separates the last number/letter on every field. Is there a way to tell it to only use the formula only for the ones containing the X?

Also, on column C I get a 'False' or 'True' instead of the X that needs to be separated from the numbers.

Appreciate all your help

dancediva,

Updated Cell B1 formula to check if A1 ends with an X:
=IF(RIGHT(A1,1)="X",LEFT(A1,LEN(A1)-1),"")

Same update to the Cell C1 formula:
=IF(RIGHT(A1,1)="X",RIGHT(A1,1),"")

As I understand, the RIGHT function can only return a string, not True/False. In my test workbook, it is properly returning the X. I have attached that workbook.

Hope this helps,
~tigeravatar

GREAT! it worked!

Thank you soooo much.... I have no idea how the formulas are done (and probably never will

so

THANK YOU!