Improve company productivity with a Business Account.Sign Up

x
?
Solved

Insert a Space in to excel if starting numbers match

Posted on 2011-03-21
8
Medium Priority
?
179 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

579 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