Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Add / Curtail spaces between characters

  Asked By: Matilda    Date: Jan 10    Category: MS Office    Views: 831
  

Could someone help me in the following:

a) I have telephone no.'s under tele column appearing as lets say 0161 000 000
or 0161 00 0000 or 161 0000 00 showing no consistency and pattern . I would
appreciate if someone can help me in
i) adding a 0 (zero) in the start of the number if it does not exist and
ii) if there is a way I can delete spaces between no.'s e.g. the above
appearing as
0161000000 in all the above scenarios
iii) Formula to add spaces after first 4 digits followed by 2 no.'s and
again some spaces and
then the remaining no. e.g. 0161 00 000

b) Outlook Express

I have my address book with 100's of addresses and I wanted to find if there is
a way I can keep a track of any new addresses added after today. I have not used
a date column before in Outlook so what is the best way to generate addresses
added after today ?

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Edjo Chalthoum     Answered On: Jan 10

The first issue we can help  with, but the second is off topic.

You don't need any VBA to format these numbers the way you want, just a
formula and a custom format in the cell. If your number is A1, you need
the following formula in another cell - say B1 - to convert it into a
number with no spaces:

=VALUE(SUBSTITUTE(A1," ",""))

Then you can go to Cells-Format - Number, select 'custom' from the list
box and type ten zeros into the field. You can put any spaces  or hyphens
you want between the zeros.

 
Didn't find what you were looking for? Find more on Add / Curtail spaces between characters Or get search suggestion and latest updates.




Tagged: