Extract the first few characters
 

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...

Extract the first few characters
 

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

"MC_blur" wrote in message
...
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...

Extract the first few characters
 

C2, copied down:

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

where B2 houses a target string.

MC_blur wrote:
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...

Extract the first few characters
 

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

"Aladin Akyurek" wrote:

C2, copied down:

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

where B2 houses a target string.

MC_blur wrote:
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...

Extract the first few characters
 

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 wrote:

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

"Aladin Akyurek" wrote:

C2, copied down:

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

where B2 houses a target string.

MC_blur wrote:
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...


--

Dave Peterson

Extract the first few characters
 

It work perfect, thank you very much!

"Dave Peterson" wrote:

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 wrote:

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

"Aladin Akyurek" wrote:

C2, copied down:

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

where B2 houses a target string.

MC_blur wrote:
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...


--

Dave Peterson

Extract the first few characters
 

That Aladin guy is pretty smart!

MC_blur wrote:

It work perfect, thank you very much!

"Dave Peterson" wrote:

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 wrote:

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

"Aladin Akyurek" wrote:

C2, copied down:

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

where B2 houses a target string.

MC_blur wrote:
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...


--

Dave Peterson


--

Dave Peterson