Extract the first few characters

[MC_blur]

Hi,
I have a excel document contain over 100 records, can i know how to extract
the character from whole column B?

Example:
1. NMBJD1234QA -> NMBJD
2. SQA1093W -> SQA

Thanks in advanced...

 

[Biff]

Hi

It appears that you want to extract everything to the left of the first
digit found in the string?

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),255,ROW(INDIRECT("1:"&LEN(A1)))))-1)

Biff

 

[Aladin Akyurek]

C2, copied down:

=LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789"))-1)

where B2 houses a target string.

 

[MC_blur]

yes, it is working fine but can filter out character "-" and "_" also?
eg: MD_S_123D -> MD
MSQ-M-129 -> MSQ
MS129M -> MS

 

[Dave Peterson]

Did you just try adding more characters to Aladin's formula?

=LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9,"-","_"},B2&"0123456789-_"))-1)

 

[MC_blur]

It work perfect, thank you very much!

Did you just try adding more characters to Aladin's formula?

=LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9,"-","_"},B2&"0123456789-_"))-1)

 

[Dave Peterson]

That Aladin guy is pretty smart!

It work perfect, thank you very much!