Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint URL

**Nebucanezars** · 10-27-2022, 07:37 AM

I have an Excel sheet attached where I have a column called "Numbers"

The next column called "Button" needs a VBA code where it does the following: Remove ABC from the "Numbers" and in case of a -1 or -2 also remove this + add the number 100.
So ABC123456 becomes 100123456 and ABC123456-1 also becomes 100123456

I did manage to get a VBA output in the column "Button" where it just removes ABC using something

Please Login or Register  to view this content.

But I struggle to create one meeting all 3 requirements.

Once this output in the column "Button" has been established I want to use this same cell for the following function (button)

Please Login or Register  to view this content.

But on cell activation it needs to open the URL google.com but with the "Button" cell added after a /
So on cell activation it needs to open the following URL: google.com/100123456

**JohnTopley** · 10-27-2022, 08:32 AM

Try

=[@Url]&"/100" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@Numbers],"ABC",""),"-1",""),"-2","")

**Nebucanezars** · 11-01-2022, 10:48 AM

Thanks a lot JohnTopley, your help is much appreciated.
The output is correct!

I have two questions left for my problem which I cant seem to figure out.

Is it possible to translate this formula into a VBA string which works for a selected column range instead of inserting this in seperate cells in the sheet itself?

Also how do I create a hyperlink from the cells output? I cant figure out how to do this with a cell containing a formula.

**Nebucanezars** · 11-11-2022, 04:55 AM

I have inserted a file with the data in column A and the button in Collumn B

The VBA Code is added under the VBA Application / Excel Objects / Sheet1

I have been trying for a week and figure out how I am able to get the follow code to work but it doesn't

On cell activation in collumn B it needs to run the following Macro: ("https://google.com" & "/100" & Substitute(Substitute(Substitute(Substitute(Substitute(A2, "ABC", ""), "-1", ""), "-2", ""), "-3", ""), "-4", ""))

Please Login or Register  to view this content.

Is there anyone who could point me in the right direction with this issue?

**JohnTopley** · 11-11-2022, 06:08 AM

This ??

Please Login or Register  to view this content.

"Replace" not "Substitute" in VBA

**Nebucanezars** · 11-11-2022, 08:24 AM

I am getting closer!

I am using the following code now

Please Login or Register  to view this content.

When I click the button the cell is showing the right output but there is no Hyperlink output.

The last thing which is needed is to add something to the VBA code that opens the hyperlink output upon click and activating the above VBA code

**jindon** · 11-11-2022, 08:45 AM

try

Please Login or Register  to view this content.

**Nebucanezars** · 11-15-2022, 06:31 AM

The code provided in the latest reply also does the trick by creating the right output upon activating the cell (see latest excel file attachment)

Please Login or Register  to view this content.

I have been reading a lot and trying to solve the last step in my problem which is the following.
After selecting the cell and creating the right output it also needs to open this output in the default browser.

I tried to add the following code on top of the code as above
But I havent been able to get it to work.

Please Login or Register  to view this content.

Is there anyone who can point me in the right direction?

**jindon** · 11-15-2022, 06:47 AM

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Nebucanezars** · 11-17-2022, 07:38 AM

Thanks for all the help, much appreciated.

Final code that solved my problem:

Please Login or Register  to view this content.

Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint URL

LinkBack

Thread Tools

Rate This Thread

Display

Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint URL

Re: Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint

Re: Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint

Re: Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint

Re: Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint

Re: Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint

Re: Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint

Re: Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint

Re: Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint

Re: Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Adding and Removing Characters in a Cell

Filepath on SharePoint drive - more than 256 characters allowed

Removing Quotes from Strings

[SOLVED] Adding characters to raw data text strings

Removing Duplicates and adding special characters

Help with removing certain strings from cells

[SOLVED] characters and strings

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions