Splitting up a cells contents

[Guest]

Hi

Have large amount of data in a column entered as A1=W123;A2=W425;A3=W2;A4=12
etc etc

I need to take the preceding W off the numerical component and have just the
number to then concenuate on. What I have to acheive is say cell A has to be
03123 rather than W123, cell A2 has to be 03425,cellA3 has to be 032 etc etc

Help would be apreciated

thanks anthony

 

[Bob Phillips]

="0"&right(A1,len(A1)-1)



--

HTH

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

 

[Roger Govier]

Hi Anthony

If you want to show leading 0's, then the cells must first be formatted as
text, or preceded by a single quote.

Alternatively, they could be Format>Cell>Number>Custom and set it to 00000
to cope with your 03123.
If you chose the latter, then you could use Find / Replace (Ctrl+h )and in
the Find pane type W, in the Replace pane type 03 and then Replace all.

Your result would be 03123, 03425, 03002, 00012

I don't know whether this is acceptable.

 

[Guest]

What about A4? Is it really 12, or you just forgot W and it should be W12?
I so
=substitute(A1,"W","03",1)

Regards,
Stefi


„Anthony†ezt írta: