CODEWITHSUNDEEP

excelreplaceexcel-formulasubstitution

Dugan

Reputation: 117

Adding variable numbers of 0's

I have a set of locations with the following naming convention:

KP 0.18 CP 02
KP 100.33 CP 06
KP 72.93 CP 09

I want to change all of the KP values so that there are 3 digits before the decimal place, e.g.:

KP 000.18 CP 02
KP 100.33 CP 06
KP 072.93 CP 09

Can anyone think of a way to do this that doesn't involve 10 nested substitute functions?

Upvotes: 0

Views: 35

Answers (2)

John Coleman

Reputation: 51998

If Your strings are in A1:A3, enter this in B1 and copy down:

=LEFT(A1,3) & REPT("0",7 - FIND(".",A1)) & RIGHT(A1,LEN(A1)- 3)

Upvotes: 3

user4039065

Reputation:

Try,

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))&TEXT(VALUE(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1),LEN(A1)))," 000.0# ")&MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1))&MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*3,LEN(A1)))

Upvotes: 1

Related Questions