| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help - Can't directly input Japanese characters into cell | Excel Discussion (Misc queries) | |||
| limiting or deleting extra characters in a cell | Excel Discussion (Misc queries) | |||
| HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
| Trendline Extract | Charts and Charting in Excel | |||
| Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) | |||
Hybrid Mode
