Solved

suffix 0 issue

Posted on 2014-10-08
3
109 Views
Last Modified: 2014-10-08
I have a column with a load of mobile phone numbers. Some are incorrect, all should begin with a 0 but some dont. I need a formula in the next column (i.e B2) to essentially say if A2 doesnt begin with 0, add a 0 to the front and then the rest of the data after the 0, or if the data does bein with a 0, do nothing and just copy over the values as is.

so 7777888888 in A2 should be come 07777888888 in B2 after the formula. Whereas 07777888888 should stay the same in B2.
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
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40368354
=IF(LEFT(A1,1)="0",A1,CONCATENATE("0",A1))
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40368355
put the below formula in B2  and then drag down


=IF(LEFT(A2,1)="0",A2,CONCATENATE("0",A2))
0
 
LVL 3

Author Comment

by:pma111
ID: 40368369
many thanks
0

Featured Post

SharePoint Admin?

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

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

718 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