Solved

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

Posted on 2014-10-09
8
198 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 167 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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 167 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 22

Assisted Solution

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

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

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!

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

733 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