Help with converting text and numbers

[Lesjoan01]

Hi,

I have quite a few large spreadsheets that contain unique reference numbers (both text and numbers) that I need to convert to a new format.

Examples of current text number formats:

ABC.1-235
LD.12-1
XYZ.356-12

Examples of what need converting to:

ABC00001.235
LD00012.1
XYZ00356.12

If have used the Replace formula, but I must be doing something wrong as I am having to manually change areas within the formula when I drag it down through the spreadsheet.

Is Replace the correct formula to use?

Can anyone help or suggest a better way to do this? Some of the spreadsheets have over 6,000 entries on them - so really need a formula that I can apply.

Many thanks

Lesley

 

[AkaTrouble]

it could be done with formula and helper cells but if only needed once i would use find and replace

select entire column and press CTRL + H keys to open find/replace dialogue box in find put "." just the fullstop and in the replace box put nothing and click replace all

then in the find box put "-" just the dash and in replace box put "." just the fullstop

based on your examples this should produce the required results

 

[sastoka]

Hi there,
do you want to replace all the dots by 4 zero and all the dash by a dot?

if yes maybe use the function text to column, define the sepration with the dot then the dash.
once done use the concatenate formula to add what you want.

or is it a changing number of zero? in this that's a little more complicated.

Regards

 

[AkaTrouble]

Hi there,
do you want to replace all the dots by 4 zero and all the dash by a dot?

if yes maybe use the function text to column, define the sepration with the dot then the dash.
once done use the concatenate formula to add what you want.

or is it a changing number of zero? in this that's a little more complicated.

Regards

i missed the 000 the 3 zero's so my solution would need to be changed on part one replace "." with "000"

 

[FormR]

so really need a formula that I can apply.

Hi, welcome to the forum! It's a bit long, but here is one formula that you can try:

Excel 2013
	A	B
2	ABC.1-235	ABC00001.235
3	LD.12-1	LD00012.1
4	XYZ.356-12	XYZ00356.12
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND(".",A2)-1)&TEXT(LEFT(SUBSTITUTE(MID(A2,FIND(".",A2)+1,10),"-",REPT(" ",10)),10),"00000")&"."&MID(A2,FIND("-",A2)+1,10)

 

[Lesjoan01]

Hi AkaTrouble,

thanks for replying. This needs to be done more than once that why a formula would be the preferred route to go down.

Kind Regards

Lesley

 

[Lesjoan01]

Hi FormR,

you star - that has worked perfectly - cannot thank you enough .

If I need anymore assistance with this would you mind if I came back to you?

Really appreciate your help

Kind regards

Lesley

 

[Lesjoan01]

Hi sastoka,

thanks for your response. FormR has managed to provide me with a solution, but thank you for taking the time to reply.

Kind regards

Lesley

 

[FormR]

Happy to help

If I need anymore assistance with this would you mind if I came back to you?

Of course not, just post back here.