Need VBA code for Searching a string of text in excel spreadsheet, applying formula, replacing

[calman91]

I have a spreadsheet with 500 entries of phone numbers on rows with spaces in the field:
exp

64 211 902 661 i need to search for alll numbers with this text string (of course it will be different numbers) , then apply formula =HYPERLINK("callto:+"& SUBSTITUTE(B2," ","")) of course not using b2 but search result , then i need to replace that cell with the value , or the hyperlink made final result should look like this callto:+64211902661 <tbody> </tbody> thank you j

<tbody>
</tbody>

 

[RCBricker]

so you want to search all 500 rows for 64 211 902 611. When found you want to replace that value with a the value from the formula =hyperlink("callto:+"& x )?

x=the value found in the cell minus the spaces
x="callto:+" & x

correct?

 

[calman91]

yes kinda , but im not searching for that number in particular, there will be many numbers all with a country code of 64

 

[calman91]

so you want to search all 500 rows for 64 211 902 611. When found you want to replace that value with a the value from the formula =hyperlink("callto:+"& x )?

x=the value found in the cell minus the spaces
x="callto:+" & x

correct?

yes kinda , but im not searching for that number in particular, there will be many numbers all with a country code of 64

 

[RCBricker]

cant you just write the formula in a different column and then paste (as values) that column over the original? I am not certain this is something that needs code to do. either it is one step to push a button or three steps to write formula, copy to end of data, copy & paste special values over destination.

wont save much time.

cell d2 = HYPERLINK("callto:+"& SUBSTITUTE(B2," ",""))

then copy all of column D and paste values over column B

 

[calman91]

cant you just write the formula in a different column and then paste (as values) that column over the original? I am not certain this is something that needs code to do. either it is one step to push a button or three steps to write formula, copy to end of data, copy & paste special values over destination.

wont save much time.

cell d2 = HYPERLINK("callto:+"& SUBSTITUTE(B2," ",""))

then copy all of column D and paste values over column B

Well, all my records 500 unique records are not in columns but rows i cant transpose to a column . so unless im wrong i cant use the fill command this is what i have record one of 500

Audddddd

Michael xxxxxxx

JOB TITLE

E-MAIL

Chief Financial Officer at xyz company

info@xyz.com

CONTACT NUMBERS

MAILING ADDRESS

TELEPHONE

61 xxxxxxxxxxxx

ADDRESS

auzzie land

CELLPHONE

39 xyz Road

ALT PHONE

CITY

NSW xxxx eeeee 2222

FACSIMILE

61 xxxvssss

COUNTRY

Australia

Call Date :

1st Email :

Verify Date :

Mail Out Date :

2nd Email :

3rd Email :

4th Email :

TQ Note

CLIENT ID FULL NAME

JOB TITLE

E-MAIL

CONTACT NUMBERS

TELEPHONE CELLPHONE ALT PHONE

FACSIMILE

MAILING ADDRESS

ADDRESS

CITY COUNTRY

Date 05-Mar-18 as you can see its all in rows not columns. I would like to just make each unique record a hyperlink for the phone number, but utimitlay, i would love to just have the records like this: Column Header . Name, company name, job title, address, phone, alt phone, email address all of this in columns and then of course adding hyperlink to the phone number columns looking something like this in table form: Name Number DISP Date Called Email Address Comments Email Sent Andrew Jackson 64xxxxxxxxx NA 2/13/2018 2:59 no answer 3 <colgroup><col><col><col><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col span="18"></colgroup><tbody>
</tbody>

but with the appropiate column headers with the addes info i need

thank you

J

 

[RCBricker]

Ahh that changes things drastically.

Search the forum as there is a solution to taking all the text from a single cell and laying it out in columns. I know I saw it awhile back. If you can't find it then I would seek that solution first and this solution afterwards.