• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

=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.
0
pma111
Asked:
pma111
  • 4
  • 2
  • 2
3 Solutions
 
ProfessorJimJamCommented:
see if it works

=IF(A1="","",IF(LEFT(A1,1)="0",A1,CONCATENATE("0",A1)))
0
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now