Simple text and number extraction

[Blade Fisher]

I have data in a string that vary from one letter and one number e.g. B9 to two letters and five numbers e.g. YF67543.

I wish to put the letter component into one cell and the number element into another. Can anyone advise what the formula for this would be please?

[Ace_XL]

In Cell C9 use this..
=IFERROR(LOOKUP(99^99,--(MID(SUBSTITUTE(B9," ",""),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(B9," ","")&"0123456789")),ROW($1:$10000)))),"")
This will extract the number portion

In D9 use
=Substitute(B9,C9,"")
This will extract the text portion

[FDibbins]

If you will ONLY have 1 or 2 letters at the start, try this...
=LEFT(B9,IF(ISNUMBER(MID(B9,2,1)*1),1,2))
then use Ace's slick lil formula for the numbers

[Tony Valko]

Will the number ever start with a zero?

B0

YF00003

[FDibbins]

With Ace's substitute, it wont matter Biff

[Tony Valko]

If the numbers will never have leading zeros then we can use this much shorter formula:

Data Range

	A	B	C
1	B2	B	2
2	C123	C	123
3	FG45	FG	45
4	XX12345	XX	12345
5	DD9	DD	9

This formula entered in B1:

=SUBSTITUTE(A1,C1,"")

This formula entered in C1:

=-LOOKUP(0,-RIGHT(A1,{1,2,3,4,5}))

Select B1:C1 and copy down as needed.

[FDibbins]

hehe you are having way too much fun with those tables now Biff

[Tony Valko]

Love those tables!

Very useful tool.

[Blade Fisher]

Hi guys,

Thanks for this. No the numbers will never start with a zero.

[Tony Valko]

You're welcome. Thanks for the feedback!