I have a cell that contains data that needs to be broken up among a few
different cells. The source cell contains data like:

COMPANYADDRESS2 VARCHAR2(35)

I need to put 35 in one cell and then VARCHAR in another. So I need a
worksheet function that finds the number between ( and ) and then I need
another function that figures out the characters between ( and the first
space to the left of the V in varchar2. Varchar2 is just an example of what
the data could be. Other examples could be number, date, etc...

I should also mention that unless the data is VARCHAR2 there is no ( to key
off of. If the value is number, date, etc... then nothing comes after it.

"hshayh0rn" wrote:

I have a cell that contains data that needs to be broken up among a few
different cells. The source cell contains data like:

COMPANYADDRESS2 VARCHAR2(35)

I need to put 35 in one cell and then VARCHAR in another. So I need a
worksheet function that finds the number between ( and ) and then I need
another function that figures out the characters between ( and the first
space to the left of the V in varchar2. Varchar2 is just an example of what
the data could be. Other examples could be number, date, etc...

Fastest way i can think of would be to do a control H and swap ( and ) for a
symbol not in use like *, then You can use a Data-Text to columns, choose
delimited, and put a check in space and special character with * in the box.
better to me especially if you have many columns making helper cells a
problem.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"hshayh0rn" wrote:

I have a cell that contains data that needs to be broken up among a few
different cells. The source cell contains data like:

COMPANYADDRESS2 VARCHAR2(35)

I need to put 35 in one cell and then VARCHAR in another. So I need a
worksheet function that finds the number between ( and ) and then I need
another function that figures out the characters between ( and the first
space to the left of the V in varchar2. Varchar2 is just an example of what
the data could be. Other examples could be number, date, etc...

On Mon, 11 Dec 2006 06:08:01 -0800, hshayh0rn
wrote:

I should also mention that unless the data is VARCHAR2 there is no ( to key
off of. If the value is number, date, etc... then nothing comes after it.


If that is the case, then what do you want as output.

One suggestion: Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ It is easily distributed with workbooks if that is an
issue.

Then use these regular expression formulas:


To return the Last Word -- defined as the last sequence starting with the first
character after a <space and ending with an <open parenthesis, or end of
line.


=REGEX.MID(A1,"(?<=\s)[^\s\(]+",-1)

To return the last set of characters that are within parentheses (and not
return the parentheses themselves), use this Regular Expression formula:

=REGEX.MID(A1,"(?<=\()[^\)]+",-1)


"hshayh0rn" wrote:

I have a cell that contains data that needs to be broken up among a few
different cells. The source cell contains data like:

COMPANYADDRESS2 VARCHAR2(35)

I need to put 35 in one cell and then VARCHAR in another. So I need a
worksheet function that finds the number between ( and ) and then I need
another function that figures out the characters between ( and the first
space to the left of the V in varchar2. Varchar2 is just an example of what
the data could be. Other examples could be number, date, etc...

--ron

Assuming your data is in A1

=IF( ISNUMBER( FIND( "VARCHAR2", A1 ) ), "VARCHAR2", RIGHT( TRIM( A1 ), LEN(
TRIM( A1 ) ) - FIND( " ", TRIM( A1 ) ) ) )

=IF( ISNUMBER( FIND( "VARCHAR2", A1 ) ), MID( A1, FIND( "(", A1 ) + 1, FIND(
")", A1 ) - FIND( "(", A1 ) - 1 ), "-" )
--
Regards,
Luc.

"Festina Lente"


"hshayh0rn" wrote:

I have a cell that contains data that needs to be broken up among a few
different cells. The source cell contains data like:

COMPANYADDRESS2 VARCHAR2(35)

I need to put 35 in one cell and then VARCHAR in another. So I need a
worksheet function that finds the number between ( and ) and then I need
another function that figures out the characters between ( and the first
space to the left of the V in varchar2. Varchar2 is just an example of what
the data could be. Other examples could be number, date, etc...

On Mon, 11 Dec 2006 10:13:01 -0800, PapaDos
wrote:

Assuming your data is in A1

=IF( ISNUMBER( FIND( "VARCHAR2", A1 ) ), "VARCHAR2", RIGHT( TRIM( A1 ), LEN(
TRIM( A1 ) ) - FIND( " ", TRIM( A1 ) ) ) )

=IF( ISNUMBER( FIND( "VARCHAR2", A1 ) ), MID( A1, FIND( "(", A1 ) + 1, FIND(
")", A1 ) - FIND( "(", A1 ) - 1 ), "-" )


Doesn't seem to work if there is a <space in COMPANYADDRESS and there is
noting in parenthesis following the number (which OP wrote was possible):

COMPANY ADDRESS2 2/5/06

I don't know if spaces are possible in COMPANYADDRESS however.





--ron