Text Question

[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

[RagDyer]

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!
==============================================



"Mike" <windme@cox.net> wrote in message
news:JFdAf.42869$0G.38518@dukeread10...
> 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
>
>
>
>

[Ron Rosenfeld]

On Fri, 20 Jan 2006 17:40:31 -0500, "Mike" <windme@cox.net> wrote:

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

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

[Mike]

Thanks RD & Ron,
Got it working, thank you!!!

Mike
"RagDyer" <RagDyer@cutoutmsn.com> wrote in message
news:enuIOshHGHA.1676@TK2MSFTNGP09.phx.gbl...
> 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!
> ==============================================
>
>
>
> "Mike" <windme@cox.net> wrote in message
> news:JFdAf.42869$0G.38518@dukeread10...
>> 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
>>
>>
>>
>>
>

[RagDyer]

You're welcome. and we appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Mike" <windme@cox.net> wrote in message
news:0wfAf.42874$0G.19614@dukeread10...
> Thanks RD & Ron,
> Got it working, thank you!!!
>
> Mike
> "RagDyer" <RagDyer@cutoutmsn.com> wrote in message
> news:enuIOshHGHA.1676@TK2MSFTNGP09.phx.gbl...
> > 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!
> > ==============================================
> >
> >
> >
> > "Mike" <windme@cox.net> wrote in message
> > news:JFdAf.42869$0G.38518@dukeread10...
> >> 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
> >>
> >>
> >>
> >>
> >
>
>