Function to strip unwanted text I don't think it is Trim

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Hello All,

What is the function to strip a line of text something like this:

file://Server/ServerHome01/UserID/Backup/Program Files/Microsoft Office/Office/Samples/Solutions.mdb

I want to delete all up to the UserID, cut that and paste it in a separate cell, I think want to take the info to the right and cut it and paste it in another adjacent cell on the same line.

As always thanks for any and all responses!

Kurt
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Try this, if that sample is consistant where the Userid is after the 4th /

This will get up to (but not including) the userid

=LEFT(A1,FIND("^",SUBSTITUTE(A1,"/","^",4)))

This will get the rest (Where above formula is in A2)

=SUBSTITUTE(A1,A2,"")
 
Upvote 0
Hello Jonmo,

You are on the right track and thanks for the prompt response.

How do I strip out the UserID? I have a long list of these and I just want the UserID in one cell and then the filename info after that in the next. I don't need the info in front.

I apologize if I wasn't clear enough.

This part does work as you state I just need this additional part.

Thanks,

Kurt
 
Upvote 0
continuing from post 2 with formulas in A2 and A3,

A2 now has the value
file://Server/ServerHome01/

A3 now has the value
UserID/Backup/Program Files/Microsoft Office/Office/Samples/Solutions.mdb

To get just the userid, use the value in A3
So in A4, put this formula
=LEFT(A3,FIND("/",A3)-1)

And to get everything past the userid in A5 put
=SUBSTITUTE(A3,A4,"")
 
Upvote 0
Hello Jonmo1 and all,

Is there any possible way to combine these steps or to use this function in a VBA macro?

Any ideas out there?

This definitely does the trick, but I want to see if it can be done in one step instead of two.

Thanks,

Kurt
 
Upvote 0
combine them like this:
=SUBSTITUTE(A3,LEFT(A3,FIND("/",A3)-1),"") <!-- / message --><!-- sig -->
 
Upvote 0
Hello cm,

That still leaves the whole string together. Where the UserID is, is where I want that in one separate cell and the rest containing the info about the file name in the next adjacent cell to the right.

Any idea on this part?

Thanks,

Kurt
 
Upvote 0
How do I find the "/" where the UserId is actually a number in the string and then place it in its own cell and then split the rest into the next adjacent cell.

I used UserID so I would not put propietary company info out there?

Thanks,

Kurt
 
Upvote 0
All strings have the following series:


file://Server/ServerHome01/UserID/Backup/Program Files/Microsoft Office/Office/Samples/Solutions.mdb

Where UserID is a 5 to 6 digit number which I want in one cell minus the info to the left and where the rest of the string is placed immediately in the next cell to the right.

I hope this helps clarify.

Thanks,

Kurt
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top