Hi
I have a bunch of cells with mixed text in them. How can I find specific text strings "https://www.domainName/images/ImageFolderName/ImageName.jpg" and replace the first slash before imageName with a space or other character?
The first part https://www.domainName/images/" are always the same and can be used to find the string.
Regards
if it was with a space
=SUBSTITUTE(A1,"/"," ",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))
The formula works if the string is alone in A1 but my problem is that the string is between some other text in the same cell.
How do I adapt the formula to find the string starting with "https://www.domainName/images/" between the other text in the cell?
you really need to attach an example with your desired before and after outcomes. Your request isn't entirely clear
Hi all- If setup as below, paste this in B2:
Please Login or Register to view this content.
Row\Col https://www.domainName/images/ aaaaaaaaaaahttps://www.domainName/images/jjjj/yyyyy.jpg aaaaaaaaaaahttps://www.domainName/images/jjjjjyyyyy.jpg
Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee
Last edited by leelnich; 08-21-2017 at 09:16 AM.
Hi davsth
The cells in column A contain html code like below. The formula should find the string "http://www.domain.co.za/Images/folder1/Image1.jpg" and remove the "/" between "folder1/Image1.jpg" of the link in all cell of column A.
Capture.PNG
Hi all- updated Post #5 formula to this:Please Login or Register to view this content.
Last edited by leelnich; 08-21-2017 at 09:16 AM.
When I use this formula in excel 2010 I get the below error.
"You've entered too many arguments for this function"
Sorry, a ")" was left out.Please Login or Register to view this content.
Last edited by leelnich; 08-21-2017 at 09:17 AM.
hi leelnich
The formula works except that for some reason it ads an extra character to the word before the "/" that was removed
Wow, I'm really airheaded today. Nice catch! Try this:Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-LeePlease Login or Register to view this content.
Thanks, That did the trick
Will this replace all links in the cell or only the first one?
As written, it only finds/affects the FIRST instance of the search string. Do you need something else?
Some of the cells contain up to 4 or 5 of these url's that needs the "/" removed. All of them start with "https://www.domainName/images/" and ends with ".jpg"
I'm not sure what will be the best way to get all of them.
If the folder name within the cell is the same for all of them, you could use SUBSTITUTE to change /foldername/ to /foldername .Please Login or Register to view this content.
Last edited by leelnich; 08-21-2017 at 10:45 AM.
Unfortunately the folder names are different for most of them.
Then I would recommend a UDF (User-defined function - VBA). I'll see what I can do...
If I know the exact string that needs to be changed will it be possible to use a find and replace formula.
Find url from "A1" in column "B1 to B100" and replace with url in column "C1"
Find url from "A2" in column "B1 to B100" and replace with url in column "C2"
Find url from "A3" in column "B1 to B100" and replace with url in column "C3"
Find url from "A4" in column "B1 to B100" and replace with url in column "C4"
Sorry, I was called away. A multi-celled solution is possible, but ugly. Here's a UDF:Paste the code in a standard module:Please Login or Register to view this content.
1) Press ALT+F11 to open the Visual Basic Editor
2) Press ALT+I to access the Insert Menu
3) Press ALT+M to select Module
4) Paste the code in the window, then close the VBE (Visual Basic Editor).
Then (assuming the main string is in A2 and the "search string" is in B1) paste this in cell B2 and copy down:NOTE: This is a MACRO, so you'll have to Save your workbook as Macro-Enabled. Select .xlsm file type when saving, then click OK.Please Login or Register to view this content.
Last edited by leelnich; 08-21-2017 at 02:08 PM.
...And here's the multi-cell formula. Paste this in B2 and drag across. Each column adjusts a new instance until all are done. Then completed column just repeats.NOTE- to verify, make columns narrow - about 12 characters wide - and turn on Wrap Text (Alignment section of Home tab)Please Login or Register to view this content.
Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee
Last edited by leelnich; 08-22-2017 at 07:09 AM.
Hi
Thanks for the solutions.
I'm trying to use the VBA but need some clarity on Main string and search string.
Will this search and replace url's in column C
If you're concerned about the "c" in the code, that's just the name of a variable (in my mind, it stands for "character"). The UDF is not cell-specific; it can be used anywhere in the "defining" workbook*. The syntax is =uFixURL(Main,Part), where Main and Part are any expressions that yield strings (text). As with all Excel functions that use strings, you can enter them as literals , cell addresses, defined Names or even a calculated result:
=uFixURL($G28,"one upon a time")
=uFixURL($A2," "&UPPER(B2&C2)&" ")
*NOTE- If the defining workbook is open, you can even use a UDF in OTHER workbooks! Just qualify the function call with its workbook name:
=YourWB.xlsm!uFixURL($G28,"one upon a time")
Last edited by leelnich; 08-22-2017 at 12:44 PM.
Hi
I've tried again with the UDF but cannot get it to work.
If I add anything in "B1" I get "#VALUE!" in the rest of column "B".
Is it possible to attach a sample sheet.
Hi
I managed to find a fix.
Thanks for taking the time and effort to help.
Happy to help, and thank you for the rep! What was the problem, and how did you fix it?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks