Parse text

M

Max

Hello All,

I have a column that is not consistent in the way data stored:

75 F1 Automatic
100 M1 Manual
94.02 C1 As requested

Note every column start with a number.

All I need is the F1, M1, C1 in the next column.

Thanks
 
R

Roger Govier

Hi Max
Ensure that you have two empty columns to the right of your data column.
Select the Column>Data>Text to Columns>Delimited>check Space>Finish.
The data you want will now be in the second column.
 
M

Max

It worked for the most part, but I found more problems like:

Medicare Fac Emp 105% T4 - Bilateral
Medicare 105% T4 - Bilateral

All I need is the T4
 
D

DILipandey

Hi Max,

Supposing you have the data in column A, use the following formula:-

=IF(ISNUMBER(FIND("F1",A1)),"F1",IF(ISNUMBER(FIND("M1",A1)),"M1",IF(ISNUMBER(FIND("C1",A1)),"C1","F1, M1, C1 not exists")))
Thanks.
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
[email protected]
[email protected]
New Delhi, India
 
R

Roger Govier

Hi Max
That's the problem if your data is not consistent.
Equally, it will be difficult to give you a formula solution, when there is
no "rule" that we can use as to where the data is that you require.

If there was always a "-" character in the cell, following the value you
want to extract, then we could use a formula entered in B1
=MID(A1,Find("-",A1)-3,2)
Copy down for as far as required.
 
R

Ron Rosenfeld

Hello All,

I have a column that is not consistent in the way data stored:

75 F1 Automatic
100 M1 Manual
94.02 C1 As requested

Note every column start with a number.

All I need is the F1, M1, C1 in the next column.

Thanks
Click to expand...

Based on what you've posted so far:

If it is the case that the string you wish to extract will always be the first
substring that consists of a capital letter followed by a single digit, then
one solution would be to download and install Longre's free morefunc.xll add-in
(use Google to find a working download site), and then use this Regular
Expression formula:

=REGEX.MID(A1,"\b[A-Z]\d\b")

If your two character string will *always* have a space before and after, then
you could also use:

=REGEX.MID(A1,"(?<=\s)[A-Z]\d(?=\s)")
--ron
 
Top