VBA Macro to separate numbers from a string

[niranjanbt]

Hi,

Let's consider that I have '1.21.4' in cell A1. I would like to extract '1' and save it in cell B1. Similarly, extract '21' and '4' and save them in cells C1 and D1 respectively.

Please let me know how to go about it.

Thanks in advance!
-NT

 

[alansidman]

Use the built-in Text to Columns function delimited by periods. Look at this link

http://www.informit.com/articles/article.aspx?p=2027553&seqNum=5

 

[Rick Rothstein]

Let's consider that I have '1.21.4' in cell A1. I would like to extract '1' and save it in cell B1. Similarly, extract '21' and '4' and save them in cells C1 and D1 respectively.

Assuming all of your values are dot delimited as your single example shows...

Sub SplitAcrossOnDots()
  Columns("A").TextToColumns Range("B1"), xlDelimited, , , False, False, False, False, True, "."
End Sub

Note: You do not need to use a macro for this... you can use Excel's Text To Columns (Data tab, Data Tools panel) functionality for this.

 

[wideboydixon]

Forget it; Rick's solution is neater

 

[niranjanbt]

Rick's solution worked for me! Thank you all