?
Solved

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

Posted on 2014-10-09
8
Medium Priority
?
219 Views
Last Modified: 2014-10-28
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
Comment
Question by:pma111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 668 total points
ID: 40370165
see if it works

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

Expert Comment

by:ProfessorJimJam
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 26

Expert Comment

by:ProfessorJimJam
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
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.

 
LVL 26

Expert Comment

by:ProfessorJimJam
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 33

Assisted Solution

by:Rob Henson
Rob Henson earned 668 total points
ID: 40370386
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
 
LVL 23

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 664 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 33

Expert Comment

by:Rob Henson
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 23

Expert Comment

by:Ejgil Hedegaard
ID: 40372848
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question