Formula Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hittin_Provs - 08 Dec 2006 19:56 GMT

I have a in Col A a list of strings which are either 2 or 3 digit
characters, and I would like to break them apart and put them in the 3
adjacent columns.

AK
34
89s

AK A K
23 2 3
89s 8 9 s

I know that I could use the Left function for the first column, but am
not sure about the other 2 characters if there are 2.

Thanks in advance,

Andrew

Reply to this Message

RagDyer - 08 Dec 2006 20:25 GMT

Select your data, then:

<Data> <Text To Columns> <Fixed Width> <Next>

In the "Data Preview" window, click at each character to place a "Break
Line" exactly where you want a new column.
Then <Finish>.

Signature

HTH,

RD

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

>I have a in Col A a list of strings which are either 2 or 3 digit
> characters, and I would like to break them apart and put them in the 3
[quoted text clipped - 14 lines]
>
> Andrew

Reply to this Message

Dave Miller - 08 Dec 2006 20:32 GMT

I believe this is what you are looking for:

if "89s" is in "A1"

=left(A1,1) 'Returns 8
=mid(A1,2,1) 'Returns 9
=right(A1,1) 'Returns s

David Miller

> > AK
> > 34
[quoted text clipped - 3 lines]
> > 23 2 3
> > 89s 8 9 s

RagDyer wote:

> Select your data, then:
>
[quoted text clipped - 29 lines]
> >
> > Andrew

Reply to this Message

bigwheel - 08 Dec 2006 20:37 GMT

Use RIGHT for the last character and MID for th one in the middle (assuming
there's a maximum for three characters)
For example, in col B you would have =LEFT(A1,1)
In col C, =IF(LEN(A1)=3,MID(A1,2,1),RIGHT(A1,1))
In col D, =IF(LEN(A1)=3,RIGHT(A1,1),"")

>I have a in Col A a list of strings which are either 2 or 3 digit
> characters, and I would like to break them apart and put them in the 3
[quoted text clipped - 14 lines]
>
> Andrew

Reply to this Message

Hittin_Provs - 08 Dec 2006 23:38 GMT

Thank you very much.,

> Use RIGHT for the last character and MID for th one in the middle (assuming
> there's a maximum for three characters)
[quoted text clipped - 20 lines]
>
> > Andrew- Hide quoted text -- Show quoted text -

Reply to this Message

Rate this thread: