How to separate value into different cells?

[Eric]

Does anyone have any suggestions on how to separate the value into different
cells?
For example, the text in cell A1 and A2 are shown below
1 CHEUNG KONG 929,000 83,737,500 3,933,462
354,652,820
196 HONGHUA GROUP 175,000 223,950 1,817,000
2,333,084

I would like to retrieve the last 4 values into
929,000 in AA1, 83,737,500 in AB1, 3,933,462 in AC1, 354,652,820 in AD1
175,000 in AA2, 223,950 in AB2, 1,817,000 in AC2, 2,333,084 in AD2

Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

 

[Jacob Skaria]

Try the below formula in cell AA1 and copy down/across as required...

=TRIM(MID(SUBSTITUTE(" " & $A1& REPT(" ",6)," ",
REPT(CHAR(32),255)),COLUMNS($B$1$1)*255,255))

 

[Eric]

Thank you very much for suggestions

Those values seem to be separated by spacing with variable space / position,
so your suggested code with specific position does not work, do you have any
suggestions on how to modify the code to retrieve those values?
Thank you very much for any suggestions
Eric

 

[Jacob Skaria]

I understand the spacing is variable...and so I have kept that as 4
spaces....(which is the least spacing in your sample data which is between
the 1st number and second number)

It worked for me when I tried with the sample data you posted....

 

[Eric]

The following example does not work
144 CHINA MER HOLD 196,000 4,947,200 6,224,438
157,394,072
Do you have any suggestions?
Thank you very much for any suggestions
Eric

 

[Eric]

Could it be possible to remove any text on the left? then retrieve each value
one by one, which are separated by spacing.
For example,
144 CHINA MER HOLD 196,000 4,947,200 6,224,438
157,394,072
It will become
196,000 4,947,200 6,224,438 157,394,072
then each number can be separated by spacing.
Do you have any suggesitons?
Thank you very much for any suggestions
Eric

 

[Ron Rosenfeld]

Does anyone have any suggestions on how to separate the value into different
cells?
For example, the text in cell A1 and A2 are shown below
1 CHEUNG KONG 929,000 83,737,500 3,933,462
354,652,820
196 HONGHUA GROUP 175,000 223,950 1,817,000
2,333,084

I would like to retrieve the last 4 values into
929,000 in AA1, 83,737,500 in AB1, 3,933,462 in AC1, 354,652,820 in AD1
175,000 in AA2, 223,950 in AB2, 1,817,000 in AC2, 2,333,084 in AD2

Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

One way would be with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula:

AA1: =SplitString($A1,-5+COLUMNS($A:A))

and fill right to AD1, then fill down as far as required.

The second argument is the "Index" into your string and the COLUMNS() argument
will adjust to compute the proper Index, where -1 is the last value, and so
forth.

The number of spaces in between the values is irrelevant, as is the number of
words in the name at the beginning.

I did assume that the various values are separated by <spaces> and that your
samples are all on one line, unlike how they came across in my news reader
where the last value appears to be preceded by a <CR>. If this is not the
case, the routine can be easily altered.

As written, the function returns the value as a string, but you can change that
to a numeric value if you wish (see comment on line 3).

=================================================
Option Explicit
Function SplitString(s As String, Optional Index As Long = 0) _
As String 'or As Double, if you prefer
Dim sTemp
sTemp = Split(Application.WorksheetFunction.Trim(s))
Select Case Index
Case Is = 0
SplitString = sTemp(0)
Case Is > 0
SplitString = sTemp(Index - 1)
Case Is < 0
SplitString = sTemp(UBound(sTemp) + 1 + Index)
End Select
End Function
==================================================
--ron