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

Hi Max
Ensure that you have two empty columns to the right of your data column.
Select the ColumnDataText to ColumnsDelimitedcheck SpaceFinish.
The data you want will now be in the second column.

--
Regards
Roger Govier

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

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

"Roger Govier" wrote:

Hi Max
Ensure that you have two empty columns to the right of your data column.
Select the ColumnDataText to ColumnsDelimitedcheck SpaceFinish.
The data you want will now be in the second column.

--
Regards
Roger Govier

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

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


New Delhi, India


"Max" wrote:

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

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.
--
Regards
Roger Govier

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

"Roger Govier" wrote:

Hi Max
Ensure that you have two empty columns to the right of your data column.
Select the ColumnDataText to ColumnsDelimitedcheck SpaceFinish.
The data you want will now be in the second column.

--
Regards
Roger Govier

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

On Fri, 3 Apr 2009 05:59:01 -0700, Max wrote:

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

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