Split information then putting it back together

[Vegas01]

Hi everyone

From the column "Creditor" I have been able to create the "Creditors No" column the issues is that there are some creditors who have the "L" as the first letter of there name.

So I was going down the road of getting rid of the names which is in column "Creditors No - Copy1" but now I am stuck.

How do I get the second line in "Creditors No - Copy1" from 403 back to L00403 taking into account that data further down creditors numbers down could need to go from 31512 to L31512.

Thanks in advance for your help.

 

[rondeondo]

Hi Vegas
If you're wanting to get from something like 403 to 00403 one option is =right("0000"&<cell ref>,5) another is =text(<cellref>,"00000").
If you want to put the L back in front, ="L"&right("0000"&<cell ref>,5) another is ="L"&text(<cellref>,"00000").

Also if you're using text to columns to split out the number, make the new column be text format rather than general, then the leading zeros will be preserved.
if you have a column with L00403 and you want to split it into two columns with the L in the first and 00403 in the next use left and right:
letter col: =left(<cellref>,1)
number col:=right(<cellref>,5)
These will maintain any leading zeros as the output is treated as text rather than a number