Solved

Insert a Space in to excel if starting numbers match

Posted on 2011-03-21
8
168 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now