Remove everything after the second space

[Deepk]

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 pm	9 10 10:55 am - 08:05 pm
9 14 10:59 am - 08:14 pm	9 10 11:30 am - 08:40 pm
7 22 12:51 pm - 08:14 pm	7 39 11:51 am - 08:42 pm	8 54 11:28 am - 08:22 pm
9 2 06:05 am - 03:18 pm	9 6 05:56 am - 03:03 pm	11 20 06:00 am - 05:20 pm
	9 23 12:49 pm - 10:12 pm	21 35 12:54 pm - 10:30 am
9 11 11:48 am - 08:59 pm	9 27 11:53 am - 09:21 pm	9 5 11:47 am - 08:52 pm
9 6 11:53 am - 08:59 pm	9 34 11:51 am - 09:26 pm	9 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.

 

[Peter_SSs]

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’)

 

[Deepk]

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.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

 

[Fluff]

How about

Sub Deepk()
   With Range("B1:D8")
      .Value = Evaluate(Replace("if(@="""","""",left(substitute(@,"" "",""."",1),find("" "",substitute(@,"" "",""."",1))))", "@", .Address))
   End With
End Sub

 

[Peter_SSs]

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.

Sub Test()
  With Range("B1", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
    .Replace What:=" ??:*", Replacement:="", LookAt:=xlPart
    .Replace What:=" ", Replacement:=".", LookAt:=xlPart
  End With
End Sub

 

[baz9d93]

How about

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!

 

[Peter_SSs]

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