Text Question

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello All,
Using Excel XP.

Is it possible to have certain letters in a text string be moved to a colum
of their own? For example I have several rows of text:
A
----------------------
1 FH
2 XH
3 FHT
4 XT
5 FT

What I want to do is to move the individual letters in each row into its own
colum, an F would always go into its own column,
X would go into its own column, H would got into his own column and T would
go into its own column. The sequence of letters into their own column will
alaways be in this order: F-X-H-T. Every row may have some of the
letters, all of the letters or none at all.

So in the above example, I want the letters to be placed like this:

A B C D E
--------------------------------------------
1 F H
2 X H
3 F H T
4 X T
5 F T

I was wondering if there a formula to accomplish this?
Thank you in advance,

Mike
 
How about entering the particular letter your looking for in Row1, as a
Column header label.

So, your datalist starts in A2, with:
B1 = F
C1 = X
D1 = H
E1 = T

Try this formula in B2:

=IF(ISNUMBER(SEARCH(B$1,$A2)),B$1,"")

And copy across to E2,
And then copy down as needed.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Hello All,
Using Excel XP.

Is it possible to have certain letters in a text string be moved to a colum
of their own? For example I have several rows of text:
A
----------------------
1 FH
2 XH
3 FHT
4 XT
5 FT

What I want to do is to move the individual letters in each row into its own
colum, an F would always go into its own column,
X would go into its own column, H would got into his own column and T would
go into its own column. The sequence of letters into their own column will
alaways be in this order: F-X-H-T. Every row may have some of the
letters, all of the letters or none at all.

So in the above example, I want the letters to be placed like this:

A B C D E
--------------------------------------------
1 F H
2 X H
3 F H T
4 X T
5 F T

I was wondering if there a formula to accomplish this?
Thank you in advance,

Mike
Click to expand...

Here's one way using regular expressions:

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Enter your strings in A2:An

3. In Row 1, starting with column B, enter the letter that should go into that
column. eg:

B1: F
C1: X
D1: H
E1: T

4. In B2 enter the formula:
=REGEX.MID($A2,B$1)

5. Select the cell and copy/drag down to Bn

6. Select the filled in cells in column B, and copy/drag across to column E


--ron
 
Back
Top