Solved

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

Posted on 2014-10-09
179 Views
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.
0
Question by:pma111
• 4
• 2
• 2

LVL 25

Accepted Solution

ProfessorJimJam earned 167 total points
ID: 40370165
see if it works

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

LVL 25

Expert Comment

ID: 40370172
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

LVL 25

Expert Comment

ID: 40370183
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

LVL 25

Expert Comment

ID: 40370203
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

LVL 31

Assisted Solution

Rob Henson earned 167 total points
ID: 40370386

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

LVL 21

Assisted Solution

Ejgil Hedegaard earned 166 total points
ID: 40371906
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

LVL 31

Expert Comment

ID: 40371919
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

LVL 21

Expert Comment

ID: 40372848
=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

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.