=IF(LEFT(A1,1)="0",A1,CONCATENATE("0",A1)) issue

I am using =IF(LEFT(A1,1)="0",A1,CONCATENATE("0",A1)) to try and add a suffix of 0 to a column of data, yet for some weird reason it isnt working if the column already starts with  a 0.

the column (A:A) is formatted general (have also formatted them text but the same issue applied), yet when I apply this formula, its returning a completely random number if the field in column A:A already start with a 0, and the forumula is only returning the correct values if the value in A:A doesnt start with 0. Any ideas why?

I was also hoping to add a 3rd clause, whereby if there is no data in column A:A i.e. a blank cell, rather than just add a 0 with nothing else in column B, to do nothing at all, i.e. leave the cell blank.
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
see if it works

=IF(A1="","",IF(LEFT(A1,1)="0",A1,CONCATENATE("0",A1)))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamCommented:
please see the attached file.  it works.

if it doesnt work in your file. i am not sure if you are putting the formula correctly.

make sure you original column do not have space. if it does then amended formula  =IF(TRIM(A1)="","",IF(LEFT(TRIM(A1),1)="0",TRIM(A1),CONCATENATE("0",TRIM(A1))))
Book1.xlsb
0
ProfessorJimJamCommented:
also another way, if your mobile numbers are always 7777888888  10 digit and you want the leading zero on those which do not have leading zeros then. you can achieve via another method.

select that column of data and then right click and select format cells then go to custom and then put eleven zeros like shown in the attached screenshot.
Capture.PNG
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

ProfessorJimJamCommented:
Also, this formula is bullet proof  

it must work in any condition   =IF(TRIM(A2)="","",IF(OR(LEFT(TRIM(A2),1)=0,LEFT(TRIM(A2),1)="0"),TRIM(A2),IF(OR(LEFT(TRIM(A2),1)<>0,LEFT(TRIM(A2),1)<>"0"),CONCATENATE("0",TRIM(A2)))))
0
Rob HensonFinance AnalystCommented:
A few comments:

Formatted General - If the entry in A physically starts with a 0 then it is a string of text even if it looks like a number.

If it were truly a number then it would not show the leading 0 unless it had some custom formatting to do so. With custom formatting the LEFT() command would ignore the formatted leading zero and return the first true character.

Formatted text - If the entry starts with a 0 then the LEFT() command should work. However, if formatted as text after the number has been entered the contents of the cell is still a number so the above logic applies.

Thanks
Rob H
0
Ejgil HedegaardCommented:
Custom format does not change the value, just the display.

To add leading zeros, and change the number to text, use this
=TEXT(A2,"00000").
Means add zeros up to text length 5.
123 and 0123 will convert to the same text 00123.
Set the number of "0" in the format to the length you want.
Numbers longer than the specified format "00000" will just convert to text.
So the number 123456 don' get leading zeros, but change to text 123456.

To add just one "0" to any number or text, also starting with "0" use
=TEXT(A2,REPT("0",LEN(A2)+1))
123 will be 0123, and 0123 will be 00123.
0
Rob HensonFinance AnalystCommented:
If none of the above are working please upload a simple workbook with samples of the values in column A, some which do work and some which don't.

Thanks
Rob
0
Ejgil HedegaardCommented:
An additional solution to add leading "0" only when numbers.
=TEXT(A2,REPT("0",LEN(A2)+IF(LEFT(A2,1)="0",0,1)))
Then the number 123 and the text 0123 will both be 0123.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.