Solved

Insert a Space in to excel if starting numbers match

Posted on 2011-03-21
8
169 Views
Last Modified: 2012-05-11
I have a list of telephone numbers and I wish to format them with a space , I wish to add a space after the 5th character if the first 5 characters match a set number of numbers.

e.g the numbers

441289307028
441482869861
442083032228

If any of the above numbers start with "4420","4423","4424","4428" then format them with a space after the 5th character.

So the results would be

441289307028
441482869861
44208 3032228

Any help would be great!






0
Comment
Question by:Envisage-Tech
  • 5
  • 3
8 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35183056
You can use this formula on a second column:

=IF(OR(LEFT(A1,4)="4420",LEFT(A1,4)="4423",LEFT(A1,4)="4424",LEFT(A1,4)="4428"),LEFT(A1;4)&" " & RIGHT(A1,7),A1)

Please check the attached example.

jppinto


Book1.xlsx
0
 
LVL 1

Author Comment

by:Envisage-Tech
ID: 35183643
Thanks Almost there. The above example deletes a character rather than inserts a space?
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35183711
You're right! I had a mistake on my formula...it should have a RIGHT(A2,8) instead of RIGHT(A2,7).

Please check the attached file.

jppinto
Book1.xlsx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:Envisage-Tech
ID: 35184021
Thank You just played around with it - do you mind If I ask you a further question?

All the numbers I have are telephone numbers and I wish to put them in the correct UK format , 3 out of the 4 formulas for the different types of telcode are below

=IF(OR(LEFT(A1,4)="4420",LEFT(A1,4)="4423",LEFT(A1,4)="4424",LEFT(A1,4)="4428"),LEFT(A1,4)&" " & RIGHT(A1,8),A1)

=IF(OR(LEFT(A1,7)="4413873",LEFT(A1,7)="4415242",LEFT(A1,7)="4415394",LEFT(A1,7)="4415395",LEFT(A1,7)="4415396",LEFT(A1,7)="4416973",LEFT(A1,7)="4416974",LEFT(A1,7)="4416977",LEFT(A1,7)="4417683",LEFT(A1,7)="4417684",LEFT(A1,7)="4417687",LEFT(A1,7)="4419467"),LEFT(A1,4)&" " & RIGHT(A1,8),A1)

=IF(OR(LEFT(A1,6)="44113",LEFT(A1,6)="44114",LEFT(A1,6)="44115",LEFT(A1,6)="44116",LEFT(A1,6)="44117",LEFT(A1,6)="44118",LEFT(A1,6)="44121",LEFT(A1,6)="44131",LEFT(A1,6)="44141",LEFT(A1,6)="44151",LEFT(A1,6)="44161",LEFT(A1,6)="44191"),LEFT(A1,6)&" " & RIGHT(A1,8),A1)

The fourth type just needs a space added after the 5th character which is a majority of all the numbers.

How do I put these formulas all together so that it produces the correct results for all types of numbers? Or am I floggin a dead horse! ;)

Thank You


0
 
LVL 33

Expert Comment

by:jppinto
ID: 35184304
Hummm...I'm not sure I understood what you're trying to do now...you want to put the 3 formulas posted all in one formula?
0
 
LVL 1

Author Comment

by:Envisage-Tech
ID: 35184391
Yes and an ifnot statement that adds a space after 5 characters...
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35184403
Just the first 3 formulas would look something like this:

=IF(OR(LEFT(A1,4)="4420",LEFT(A1,4)="4423",LEFT(A1,4)="4424",LEFT(A1,4)="4428"),LEFT(A1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1,7)="4413873",LEFT(A1,7)="4415242",LEFT(A1,7)="4415394",LEFT(A1,7)="4415395",LEFT(A1,7)="4415396",LEFT(A1,7)="4416973",LEFT(A1,7)="4416974",LEFT(A1,7)="4416977",LEFT(A1,7)="4417683",LEFT(A1,7)="4417684",LEFT(A1,7)="4417687",LEFT(A1,7)="4419467"),LEFT(A1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1,6)="44113",LEFT(A1,6)="44114",LEFT(A1,6)="44115",LEFT(A1,6)="44116",LEFT(A1,6)="44117",LEFT(A1,6)="44118",LEFT(A1,6)="44121",LEFT(A1,6)="44131",LEFT(A1,6)="44141",LEFT(A1,6)="44151",LEFT(A1,6)="44161",LEFT(A1,6)="44191"),LEFT(A1,6)&" " & RIGHT(A1,8),A1)))

If you want to add an aditional formula, it would look even worst!
0
 
LVL 33

Accepted Solution

by:
jppinto earned 500 total points
ID: 35184497
=IF(OR(LEFT(A1,4)="4420",LEFT(A1,4)="4423",LEFT(A1,4)="4424",LEFT(A1,4)="4428"),LEFT(A1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1,7)="4413873",LEFT(A1,7)="4415242",LEFT(A1,7)="4415394",LEFT(A1,7)="4415395",LEFT(A1,7)="4415396",LEFT(A1,7)="4416973",LEFT(A1,7)="4416974",LEFT(A1,7)="4416977",LEFT(A1,7)="4417683",LEFT(A1,7)="4417684",LEFT(A1,7)="4417687",LEFT(A1,7)="4419467"),LEFT(A1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1,6)="44113",LEFT(A1,6)="44114",LEFT(A1,6)="44115",LEFT(A1,6)="44116",LEFT(A1,6)="44117",LEFT(A1,6)="44118",LEFT(A1,6)="44121",LEFT(A1,6)="44131",LEFT(A1,6)="44141",LEFT(A1,6)="44151",LEFT(A1,6)="44161",LEFT(A1,6)="44191"),LEFT(A1,6)&" " & RIGHT(A1,8),LEFT(A1,4)& " " & RIGHT(A1,8))))

0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

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.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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