QUERY REGARDING Functions in Columns in Excel

[reachkiran03]

Hai Kiran here I always keep my friends mobile numbers in excel sheet like example 99800 12890. When I want to send wishes in some sites they will take mobile numbers if it is written as 9980012890.I have kept like 99800 12890. Now how to eraze the gap or delete the space which i have given in all the mobile numbers at one go by using an command or functions.


2)I would like to add 91 before the 9980012890 for all the 100 numbers which i have typed in excel. what is the command to do so.

 

[Hui]

Kiran


Firstly, Welcome to the Chandoo.org Forums


Q1. Select the Range holding the Phone Numbers

Ctrl H

Enter a Space in the Find What:

Leave the Replace With: blank

Apply


Q2. I will assume your numbers are in A2:A100

In the cell next to the first number B2: enter ="91"&A2

Copy down

Select Column B

Copy and Paste Values over itself

Delete Column A


or


If all the Phone Numbers have 10 digits like your example

You can type 910000000000 in a blank cell

Copy the cell

Select Column A

Paste Special

Values, Add

Apply

Only use this if they all have 10 Digits

 

[reachkiran03]

Hui Sir thanks for your reply i tried your 1 &2 method it is working.

 

[reachkiran03]

Dear all, Query 1) /098862 06525(m),23581500® few numbers of my clients i have stored in the above mentioned format,now i would like to segregate these numbers as mobile numbers in one column & landline numbers in separate column. 98862 06525,23581500 .

Iwould like to remove / (m) (R) in both the columns please help me to do so.

 

[oldchippy]

Hi,


Assuming starting in column A, then this in B1


=LEFT(A1,FIND("(",A1)-1)


And this in C1


=MID(A1,FIND(",",A1)+1,LEN(A1)-1-FIND(",",A1))