Hello,

I was wondering if there is a function similar to chunk expressions? In my
case I have cells in column A that start with a number and is followed by
sevral words. All I want to do is extract the number (as a number) and the
associated number in the column beside it into an array.

Column A Column B
145 This is a test 54
234 more of it 230

I would like an array for each cell in Range("A1:A300")

145,54
234,230
....
In some languages I would just use a "word" expression ... ie put word 1 of
cell A1 & "," and cell B1

Is there something similar in Excel?

thanks.

=left(A1,3) & "," & B1

if the number of digits could be variable

=Left(A1,find(" ",A1)-1) & "," & B1

assumet that formula is in C1. Select c1 and drag fill down the column.

--
Regards,
Tom Ogilvy


"Glen" wrote in message
...
Hello,

I was wondering if there is a function similar to chunk expressions? In my
case I have cells in column A that start with a number and is followed by
sevral words. All I want to do is extract the number (as a number) and the
associated number in the column beside it into an array.

Column A Column B
145 This is a test 54
234 more of it 230

I would like an array for each cell in Range("A1:A300")

145,54
234,230
...
In some languages I would just use a "word" expression ... ie put word 1
of
cell A1 & "," and cell B1

Is there something similar in Excel?

thanks.

Hi Tom,

Sorry that I had forgotten to mention that th enumbers were variable in
length but you caught it.

I am guessing that I might be able to create my own "chunk" function based
on what you have given ... ie so I could just input the number of "chunks"
that I want from a cell.

If true then is there a way to make it a global function or would I need to
put the code in each module?

thanks,

Glen

If you put it in a general module, it will be global to the workbook

If you put it in a general module, make the workbook and addin and then load
the addin, it will be global to any open workbooks - like the analysis
toolpak (which is an addin) when loaded.

Here is a link to an article on Addins

http://www.jkp-ads.com/articles/DistributeMacro00.htm

--
Regards,
Tom Ogilvy


"Glen" wrote in message
...
Hi Tom,

Sorry that I had forgotten to mention that th enumbers were variable in
length but you caught it.

I am guessing that I might be able to create my own "chunk" function based
on what you have given ... ie so I could just input the number of "chunks"
that I want from a cell.

If true then is there a way to make it a global function or would I need
to
put the code in each module?

thanks,

Glen

One other thought comes to mind. How to handle delimiters within text in
cells or variables.

Is it possible to define a globaldelimiter for example a "," or a "/".

thanks

If you are working in VBA, then use the SPLIT() function. It will both
separate a phrase into individual words/numbers and will allow arbitrary
field separators.
--
Gary's Student


"Glen" wrote:

One other thought comes to mind. How to handle delimiters within text in
cells or variables.

Is it possible to define a globaldelimiter for example a "," or a "/".

thanks