Separating / extracting words within a cell

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Tom Ogilvy]

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