remove text from string

[crj510]

Hi,

I have a string that I want to remove some text from;

11.2.B.9 XXXXXXXXXX - I want to remove the 2, the B, and the 9 separetly. The first number can be one or two digits, same with the second number, same with the third number. The letter is always a single letter. the text at the end is not consistent at all.

here is the function I have to pull out the "2"...the problem is it does not work with a two digit number: =MID(A1,FIND(".",A1,FIND(".",A1))+1,FIND(".",A1,FIND(".",A1,FIND(".",A1,FIND(".",A1))))-2)

I can figure out the rest of the parses if I can get some help pulling out the 2.

thanks!

[TMS]

=--MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)

will make it numeric

Regards

[crj510]

thanks for the help, it worked! It is actually for a calculated column in sharepoint, so text to column won't work here.

[crj510]

Any chances I can get some help on the rest of the functions I need? I am still wanting to get the "B" and the "9" out on their own from the question above.

I have tried to figure this out, but my rookie excel skills are holding me back.

thanks again!

[martindwilson]

why not just parse it out using text to columns?

[TMS]

In order:

=--LEFT(A1,FIND(".",A1)-1)
=--MID(A1,  FIND(".",A1)+1,  FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)
=MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-FIND(".",A1,FIND(".",A1)+1)-1)
=--MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,FIND(" ",A1) - FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1) - 1)

Regards

[martindwilson]

so its calculated? are you splitting the calculated string? why not calculate each bit at source