Remove everything after the second space

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I have a huge dataset where I need a VBA code to remove everything after the second space. I don't want a formula for it as my data is in several consecutive columns. The data starts from column 'B' onwards. It may have blanks as well. A sample of input-output is provided below.

Input data:
B​
C​
D​
7 47 10:12 am - 06:00 pm9 10 10:55 am - 08:05 pm
9 14 10:59 am - 08:14 pm9 10 11:30 am - 08:40 pm
7 22 12:51 pm - 08:14 pm7 39 11:51 am - 08:42 pm8 54 11:28 am - 08:22 pm
9 2 06:05 am - 03:18 pm9 6 05:56 am - 03:03 pm11 20 06:00 am - 05:20 pm
9 23 12:49 pm - 10:12 pm21 35 12:54 pm - 10:30 am
9 11 11:48 am - 08:59 pm9 27 11:53 am - 09:21 pm9 5 11:47 am - 08:52 pm
9 6 11:53 am - 08:59 pm9 34 11:51 am - 09:26 pm9 47 11:56 am - 09:44 pm

Output data:
B​
C​
D​
7.47​
9.1​
9.14​
9.1​
7.22​
7.39​
8.54​
9.2​
9.6​
11.2​
9.23​
21.35​
9.11​
9.27​
9.5​
9.6​
9.34​
9.47​

Please help me with the code. Thank you in advance.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Better try again to post the output.

Also, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Better try again to post the output.

Also, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Peter, thank you for your response.

Profile updated.

Since the system not allowing me to edit my post, I am posting the output here. Please review.

B​
C​
D​
7.479.1
9.149.1
7.227.398.54
9.29.611.2
9.2321.35
9.119.279.5
9.69.349.47
 
Upvote 0
How about
VBA Code:
Sub Deepk()
   With Range("B1:D8")
      .Value = Evaluate(Replace("if(@="""","""",left(substitute(@,"" "",""."",1),find("" "",substitute(@,"" "",""."",1))))", "@", .Address))
   End With
End Sub
 
Upvote 0
Since the system not allowing me to edit my post,
Looks like you were able to in the end. :)

Profile updated.
Thanks for that.

Here is another approach that you could test/consider.

VBA Code:
Sub Test()
  With Range("B1", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
    .Replace What:=" ??:*", Replacement:="", LookAt:=xlPart
    .Replace What:=" ", Replacement:=".", LookAt:=xlPart
  End With
End Sub
 
Upvote 0
How about
VBA Code:
Sub Deepk()
   With Range("B1:D8")
      .Value = Evaluate(Replace("if(@="""","""",left(substitute(@,"" "",""."",1),find("" "",substitute(@,"" "",""."",1))))", "@", .Address))
   End With
End Sub

thanks for this code, I have been trying to use this on my sheet for over 2 hours now, but I need to delete everything after 5 spaces - i am adding extra "" but getting confused I can get it to remove after 1 space and 2 as you have shown but anything else I am trying only getting #VALUE!
 
Upvote 0
I suspect that the code above will not be the best for your situation or easily adapted to it. I suggest that you start your own new thread and detail your requirements there as well as giving some representative sample data and the expected results, preferably with XL2BB
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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