Split Text

[Guest]

I am trying to split data from one cell to another, I tried using the TEXT To
COLUMNS wizard but it wants to split the cell as a formula instead of the
value.

A1 =Sheet1!A1
A1 displays the value (please,help)

How can I split the value of A1 into A2 and A3?

Any help is greatly appriciated. Thanks.

 

[RagDyer]

Try these text formulas:

In B1,
=LEFT(A1,FIND(",",A1)-1)

In C1,
=RIGHT(A1,LEN(A1)-FIND(",",A1))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Bob Phillips]

Change column A to values first

Select column A, then Copy
Edit>Pastespecial>Values

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

That worked pretty good, but now my format is messed up? I'm looking to
prefix the result with an "E" how would I do that? Before I just used a
custom format of "E"#### that worked pretty good but when I use the formula
it doesn't work. How can prefix it with an "E" for the following?

A1 =Sheet1!A1
A1 displays the value (GE00205189)
B1 =RIGHT(A1,4)
B1 displays the value (5189)

Thanks.

 

[RagDyer]

="E"&LEFT(A1,FIND(",",A1)-1)

="E"&RIGHT(A1,LEN(A1)-FIND(",",A1))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[pinmaster]

="E"&RIGHT(A1,4)

Regards
JG

 

[Guest]

The cell currently outputs a text string that looks like a number, so it is
formatted as text (a format like "E"#### applys only to numbers).

You have three options.

I. Change the formula to output a number.
=VALUE(RIGHT(A1,4))
and use the same custom number format of
"E"####

II. Leave the formula alone
=RIGHT(A1,4)
and change the format to something like
#;-#;0;"E"@

II. Change the formula to output a text string that includes "E".
="E"&RIGHT(A1,4)
and format as
general