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 [TABLE="width: 540"]
<tbody>[TR]
[TD="width: 540"] 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 [TABLE="width: 117"]
<tbody>[TR]
[TD="class: xl65, width: 117"]callto:+64211902661[/TD]
[/TR]
</tbody>[/TABLE]

thank you

j[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 1152"]
<colgroup><col span="18"></colgroup><tbody>[TR]
[TD="colspan: 5"]Audddddd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 15"]Michael xxxxxxx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOB TITLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E-MAIL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 18"]Chief Financial Officer at xyz company[/TD]
[/TR]
[TR]
[TD="colspan: 18"]info@xyz.com[/TD]
[/TR]
[TR]
[TD="colspan: 2"]CONTACT NUMBERS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]MAILING ADDRESS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]TELEPHONE[/TD]
[TD="colspan: 5"]61 xxxxxxxxxxxx[/TD]
[TD="colspan: 4"]ADDRESS[/TD]
[TD="colspan: 5"]auzzie land[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]CELLPHONE[/TD]
[TD="colspan: 5"] [/TD]
[TD="colspan: 4"] [/TD]
[TD="colspan: 5"]39 xyz Road[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]ALT PHONE[/TD]
[TD="colspan: 5"] [/TD]
[TD="colspan: 4"]CITY[/TD]
[TD="colspan: 5"]NSW xxxx eeeee 2222[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]FACSIMILE[/TD]
[TD="colspan: 5"]61 xxxvssss [/TD]
[TD="colspan: 4"]COUNTRY[/TD]
[TD="colspan: 5"]Australia[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Call Date :[/TD]
[TD="colspan: 5"]1st Email :[/TD]
[TD="colspan: 3"]Verify Date :[/TD]
[TD="colspan: 3"]Mail Out Date :[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]2nd Email :[/TD]
[TD="colspan: 5"]3rd Email :[/TD]
[TD="colspan: 3"]4th Email :[/TD]
[TD="colspan: 3"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TQ Note[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]CLIENT ID FULL NAME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOB TITLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E-MAIL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]CONTACT NUMBERS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]TELEPHONE CELLPHONE ALT PHONE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FACSIMILE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]MAILING ADDRESS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ADDRESS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CITY COUNTRY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]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:

[TABLE="width: 869"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD="colspan: 2"]Number[/TD]
[TD]DISP[/TD]
[TD]Date Called[/TD]
[TD]Email Address[/TD]
[TD]Comments[/TD]
[TD]Email Sent[/TD]
[/TR]
[TR]
[TD]Andrew Jackson[/TD]
[TD="colspan: 2"]64xxxxxxxxx[/TD]
[TD]NA[/TD]
[TD="align: right"]2/13/2018 2:59[/TD]
[TD][/TD]
[TD]no answer 3


[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.