Add leading 0 only after letters

backgroundbook · Feb 18th 2020

I have data that looks like this: CI-1, CK-08, H-03, J-12, CM-11, B-8

I want all the numerical characters from 1 to 9 to have 0s before them with the letters unchanged.

Actual	Result
CI-1	CI-01
CK-08	CK-08
H-03	H-03
B-8	B-08

I am able to do this through several separated formulas (step by step; separating the letters from the numbers, adding the leading 0, concatenating them back together) however, this is taking too long to compute.

**Carim** · Feb 21st 2020

Hello,

Attached is your test file

Hope this will help

bosco_yip · Feb 23rd 2020

Or,

B2, copied down :

=LEFT(A2,FIND("-",A2))&TEXT(LOOKUP(9^9,-RIGHT(A2,ROW($1:$100))),"[<10]00")

Regards

**Carim** · Mar 4th 2020

Quote

FORUM ETIQUETTE
4a.
4b.
4c. Please take the time to thank those who took their time to help you..

Why? Because every single person that helps out at Ozgrid does so on their own free time. This is a free to post forum so show some gratitude. Besides, its nice to be nice and you will find that people will help you, if you help them by being a good responsible forum citizen.

Add leading 0 only after letters

Participate now!

Share