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