Hi
I have cells in column B and in each cell is a persons first name and last name.
The first and last name is connected by a "_".
Example "John_Doe"
I want to go from Cell b2 down and remove the "_" and everything else to the right, leaving just the first name in the cell.
How can I do this with VBA?
There is absolutely not need for macro or formula for something like this:
1. Select the entire column with the name
2. Use Ctrl+H to open a Find and Replace tool
3. Type in _* in Find what: field
4. Leave Replace with: blank and press Replace All.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Maybe:
Sub swade730y() Columns(2).Replace "_*", "" End Sub
Tha would remove the "_". I want to keep what is after the "_" or What is before the "_" (First OR Last Name)
Now you contradict your original requirements: "I want to go from Cell b2 down and remove the "_" and everything else to the right, leaving just the first name in the cell."Originally Posted by swade730
I got something like a few data
12345/1
12345/1 , 56778/1
i only want remove the things after "/"
how do i do that keeping the comma and the other digits
Alkey give you the solution for that in #2
There is absolutely not need for macro or formula for something like this:
1. Select the entire column with the name
2. Use Ctrl+H to open a Find and Replace tool
3. Type in /* in Find what: field
4. Leave Replace with: / and press Replace All.
Last edited by oeldere; 06-02-2015 at 02:24 AM. Reason: Replace with /
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
as in i have data such as 1234/1, 0340304/2 and the results should be something like 1234, 0340304
You get better help if you add a small excel file, without confidential information.
Please add manualy the expected results in the file.
Hi icecreame,
You can use this formula - just change the cell reference to suit:
=LEFT(A2,SEARCH("/",A2)-1)
Regards,
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Hi Robert/Oeldere,
Thanks for your responsive reply but i cant get the right data i want, the formula keeps removing the data that i even wanted so I was having 1234/1 , 43240/23 after using the formula im left with 1234 only but i want both 1234 , 43240 any idea how to do it?
Regards
Icereame
Not sure as it works for me
Check that the calculation method of the workbook is set to automatic.
yeah it is automatic not sure why man
Seeing the workbook should provide the answerYou get better help if you add a small excel file, without confidential information.
Please add manually the expected results in the file.
I will provide this
@ icecreame, Maybe:
Sub icecreame() Dim rcell As Range For Each rcell In Range("A2:A" & Range("A" & Rows.Count).End(3).row) rcell.Value = Val(rcell) Next rcell End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks