software

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 -gt; NMBJD
2. SQA1093W -gt; 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(quot;1: quot;amp;LEN(A1))),1))),255,ROW(INDIRECT(quot;1:quot;amp;LEN(A1) ))))-1)

Biff

quot;MC_blurquot; gt; wrote in message
...
gt; Hi,
gt; I have a excel document contain over 100 records, can i know how to
gt; extract
gt; the character from whole column B?
gt;
gt; Example:
gt; 1. NMBJD1234QA -gt; NMBJD
gt; 2. SQA1093W -gt; SQA
gt;
gt; Thanks in advanced...
C2, copied down:

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

where B2 houses a target string.

MC_blur wrote:
gt; Hi,
gt; I have a excel document contain over 100 records, can i know how to extract
gt; the character from whole column B?
gt;
gt; Example:
gt; 1. NMBJD1234QA -gt; NMBJD
gt; 2. SQA1093W -gt; SQA
gt;
gt; Thanks in advanced...

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

quot;Aladin Akyurekquot; wrote:

gt; C2, copied down:
gt;
gt; =LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2amp;quot;0123 456789quot;))-1)
gt;
gt; where B2 houses a target string.
gt;
gt; MC_blur wrote:
gt; gt; Hi,
gt; gt; I have a excel document contain over 100 records, can i know how to extract
gt; gt; the character from whole column B?
gt; gt;
gt; gt; Example:
gt; gt; 1. NMBJD1234QA -gt; NMBJD
gt; gt; 2. SQA1093W -gt; SQA
gt; gt;
gt; gt; Thanks in advanced...
gt;

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,quot;-quot;,quot;_quot;},B2amp;quot;0123456789-_quot;))-1)
MC_blur wrote:
gt;
gt; yes, it is working fine but can filter out character quot;-quot; and quot;_quot; also?
gt; eg: MD_S_123D -gt; MD
gt; MSQ-M-129 -gt; MSQ
gt; MS129M -gt; MS
gt;
gt; quot;Aladin Akyurekquot; wrote:
gt;
gt; gt; C2, copied down:
gt; gt;
gt; gt; =LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2amp;quot;0123 456789quot;))-1)
gt; gt;
gt; gt; where B2 houses a target string.
gt; gt;
gt; gt; MC_blur wrote:
gt; gt; gt; Hi,
gt; gt; gt; I have a excel document contain over 100 records, can i know how to extract
gt; gt; gt; the character from whole column B?
gt; gt; gt;
gt; gt; gt; Example:
gt; gt; gt; 1. NMBJD1234QA -gt; NMBJD
gt; gt; gt; 2. SQA1093W -gt; SQA
gt; gt; gt;
gt; gt; gt; Thanks in advanced...
gt; gt;

--

Dave Peterson

It work perfect, thank you very much!

quot;Dave Petersonquot; wrote:

gt; Did you just try adding more characters to Aladin's formula?
gt;
gt; =LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9,quot;-quot;,quot;_quot;},B2amp;quot;0123456789-_quot;))-1)
gt;
gt;
gt;
gt; MC_blur wrote:
gt; gt;
gt; gt; yes, it is working fine but can filter out character quot;-quot; and quot;_quot; also?
gt; gt; eg: MD_S_123D -gt; MD
gt; gt; MSQ-M-129 -gt; MSQ
gt; gt; MS129M -gt; MS
gt; gt;
gt; gt; quot;Aladin Akyurekquot; wrote:
gt; gt;
gt; gt; gt; C2, copied down:
gt; gt; gt;
gt; gt; gt; =LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2amp;quot;0123 456789quot;))-1)
gt; gt; gt;
gt; gt; gt; where B2 houses a target string.
gt; gt; gt;
gt; gt; gt; MC_blur wrote:
gt; gt; gt; gt; Hi,
gt; gt; gt; gt; I have a excel document contain over 100 records, can i know how to extract
gt; gt; gt; gt; the character from whole column B?
gt; gt; gt; gt;
gt; gt; gt; gt; Example:
gt; gt; gt; gt; 1. NMBJD1234QA -gt; NMBJD
gt; gt; gt; gt; 2. SQA1093W -gt; SQA
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks in advanced...
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

That Aladin guy is pretty smart!

MC_blur wrote:
gt;
gt; It work perfect, thank you very much!
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Did you just try adding more characters to Aladin's formula?
gt; gt;
gt; gt; =LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9,quot;-quot;,quot;_quot;},B2amp;quot;0123456789-_quot;))-1)
gt; gt;
gt; gt;
gt; gt;
gt; gt; MC_blur wrote:
gt; gt; gt;
gt; gt; gt; yes, it is working fine but can filter out character quot;-quot; and quot;_quot; also?
gt; gt; gt; eg: MD_S_123D -gt; MD
gt; gt; gt; MSQ-M-129 -gt; MSQ
gt; gt; gt; MS129M -gt; MS
gt; gt; gt;
gt; gt; gt; quot;Aladin Akyurekquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; C2, copied down:
gt; gt; gt; gt;
gt; gt; gt; gt; =LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2amp;quot;0123 456789quot;))-1)
gt; gt; gt; gt;
gt; gt; gt; gt; where B2 houses a target string.
gt; gt; gt; gt;
gt; gt; gt; gt; MC_blur wrote:
gt; gt; gt; gt; gt; Hi,
gt; gt; gt; gt; gt; I have a excel document contain over 100 records, can i know how to extract
gt; gt; gt; gt; gt; the character from whole column B?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Example:
gt; gt; gt; gt; gt; 1. NMBJD1234QA -gt; NMBJD
gt; gt; gt; gt; gt; 2. SQA1093W -gt; SQA
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks in advanced...
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson