Extract the first few characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
 
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
 
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.
 
yes, it is working fine but can filter out character "-" and "_" also?
eg: MD_S_123D -> MD
MSQ-M-129 -> MSQ
MS129M -> MS
 
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)
 
It work perfect, thank you very much!

Dave Peterson said:
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)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract characters from Right of string 5
The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database 0
Text Extraction (parsing) 3
Extract one character 5
Extract all text without spaces and special characters 4
Postcode Area extract first alpha letters 6
Extract numeric characters plus one character... 15
Excel Extract Text from decimal separated string 6

Back
Top