Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the column
and post processing one column to retain numbers and another column to retain
text.

--
novastar

With your text in cell A1; try this formula in B1 .Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1) ),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:

Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the column
and post processing one column to retain numbers and another column to retain
text.

--
novastar

This will extract the last "word" in a cell:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Note that the result is a *TEXT* string even if the last "word" in the cell
is a number. If your numbers can contain leading 0s then you'll need to
extract them as text in order to retain the leading 0s.

--
Biff
Microsoft Excel MVP


"novastar" wrote in message
...
Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or
lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the
column
and post processing one column to retain numbers and another column to
retain
text.

--
novastar

The previous one will return an error for a lenghty string. Try this array
formula
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$99),1 )),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With your text in cell A1; try this formula in B1 .Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1) ),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:

Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the column
and post processing one column to retain numbers and another column to retain
text.

--
novastar

Hi T.Valco,

Thanks........this helps me get the numbers in a column..

Now....I would like to eliminate the numbers and extract only the text in a
different column...how do i do this?
--
novastar


"T. Valko" wrote:

This will extract the last "word" in a cell:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Note that the result is a *TEXT* string even if the last "word" in the cell
is a number. If your numbers can contain leading 0s then you'll need to
extract them as text in order to retain the leading 0s.

--
Biff
Microsoft Excel MVP


"novastar" wrote in message
...
Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or
lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the
column
and post processing one column to retain numbers and another column to
retain
text.

--
novastar

In B1
=SUBSTITUTE(A1,C1,)
In C1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:

Hi T.Valco,

Thanks........this helps me get the numbers in a column..

Now....I would like to eliminate the numbers and extract only the text in a
different column...how do i do this?
--
novastar


"T. Valko" wrote:

This will extract the last "word" in a cell:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Note that the result is a *TEXT* string even if the last "word" in the cell
is a number. If your numbers can contain leading 0s then you'll need to
extract them as text in order to retain the leading 0s.

--
Biff
Microsoft Excel MVP


"novastar" wrote in message
...
Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or
lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the
column
and post processing one column to retain numbers and another column to
retain
text.

--
novastar

Let's assume you have this formula to extract the numbers in cell B1:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Enter this formula in C1 to return only the text portion of cell A1:

=TRIM(SUBSTITUTE(A1,B1,""))

--
Biff
Microsoft Excel MVP


"novastar" wrote in message
...
Hi T.Valco,

Thanks........this helps me get the numbers in a column..

Now....I would like to eliminate the numbers and extract only the text in
a
different column...how do i do this?
--
novastar


"T. Valko" wrote:

This will extract the last "word" in a cell:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Note that the result is a *TEXT* string even if the last "word" in the
cell
is a number. If your numbers can contain leading 0s then you'll need to
extract them as text in order to retain the leading 0s.

--
Biff
Microsoft Excel MVP


"novastar" wrote in message
...
Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or
lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it
into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the
column
and post processing one column to retain numbers and another column to
retain
text.

--
novastar

Hi Jacob,

This did not work for me. If I have a cell containing "Adam 22", I would
like to separate "adam" in one column and "22" in another column.

With the formula below I get "#N/A" in the cell.

--
novastar


"Jacob Skaria" wrote:

The previous one will return an error for a lenghty string. Try this array
formula
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$99),1 )),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With your text in cell A1; try this formula in B1 .Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1) ),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:

Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the column
and post processing one column to retain numbers and another column to retain
text.

--
novastar

You are entering the formula as normal. Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:

Hi Jacob,

This did not work for me. If I have a cell containing "Adam 22", I would
like to separate "adam" in one column and "22" in another column.

With the formula below I get "#N/A" in the cell.

--
novastar


"Jacob Skaria" wrote:

The previous one will return an error for a lenghty string. Try this array
formula
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$99),1 )),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With your text in cell A1; try this formula in B1 .Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1) ),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:

Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the column
and post processing one column to retain numbers and another column to retain
text.

--
novastar

Hi Jacob,

Thanks....this helps.

Thanks to both Jacob and T.Valko...you helped me fix an issue I have been
struggling with for years. Now I don;t have to go to UNIX and stay in
excel...:-)

Thanks again.
--
novastar


"Jacob Skaria" wrote:

In B1
=SUBSTITUTE(A1,C1,)
In C1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:

Hi T.Valco,

Thanks........this helps me get the numbers in a column..

Now....I would like to eliminate the numbers and extract only the text in a
different column...how do i do this?
--
novastar


"T. Valko" wrote:

This will extract the last "word" in a cell:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Note that the result is a *TEXT* string even if the last "word" in the cell
is a number. If your numbers can contain leading 0s then you'll need to
extract them as text in order to retain the leading 0s.

--
Biff
Microsoft Excel MVP


"novastar" wrote in message
...
Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or
lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the
column
and post processing one column to retain numbers and another column to
retain
text.

--
novastar

Hi,

You could try 'text to columns' ... and save it as csv.

But if you have to post-process it with unix, why don't making a shell
script that does it all?

I suppose it is a 'text' file like most unix files a
- sed 's/ /,/g' file1 file2 will replace the spaces by ","

Keep in mind that Unix is 7-bit oriented ans Windows/Dos is 8:
dos2unix file file1 ( I think, use man dos2unix in unix environment)

What is the added value of post processing in Unix?

If you don't mind to send me a sample and explaining what your outcome must
be, I will have a look into.



Wkr,

JP

"novastar" wrote in message
...
Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or
lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the
column
and post processing one column to retain numbers and another column to
retain
text.

--
novastar

Hi Jacob,

My bad. I was wrong. I did what you said and it works just fine.

Thanks much!
--
novastar


"Jacob Skaria" wrote:

You are entering the formula as normal. Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:

Hi Jacob,

This did not work for me. If I have a cell containing "Adam 22", I would
like to separate "adam" in one column and "22" in another column.

With the formula below I get "#N/A" in the cell.

--
novastar


"Jacob Skaria" wrote:

The previous one will return an error for a lenghty string. Try this array
formula
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$99),1 )),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With your text in cell A1; try this formula in B1 .Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1) ),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:

Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the column
and post processing one column to retain numbers and another column to retain
text.

--
novastar

On Fri, 21 Aug 2009 09:37:01 -0700, novastar
wrote:

Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the column
and post processing one column to retain numbers and another column to retain
text.

If the text always comes first, and the number last, then

For text:

=TRIM(LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1 ,2,3,4,5,6,7,8,9,0"))-1))

For the numeric portion (with the number returned as text, which would include
leading zero's):

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4 ,5,6,7,8,9,0")),99)

or, to return the value as numeric:

=--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4, 5,6,7,8,9,0")),99)

--ron

Hi Jacob thanks a lot u r Genius man thx so much

On Friday, August 21, 2009 12:37 PM novastar wrote:

Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I do not mind copying the column
and post processing one column to retain numbers and another column to retain
text.

--
novastar


On Friday, August 21, 2009 12:44 PM Jacob Skaria wrote:

With your text in cell A1; try this formula in B1 .Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1) ),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:


On Friday, August 21, 2009 12:54 PM T. Valko wrote:

This will extract the last "word" in a cell:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Note that the result is a *TEXT* string even if the last "word" in the cell
is a number. If your numbers can contain leading 0s then you will need to
extract them as text in order to retain the leading 0s.

--
Biff
Microsoft Excel MVP


On Friday, August 21, 2009 1:03 PM Jacob Skaria wrote:

The previous one will return an error for a lenghty string. Try this array
formula
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$99),1 )),0),99)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:


On Friday, August 21, 2009 1:31 PM novastar wrote:

Hi T.Valco,

Thanks........this helps me get the numbers in a column..

Now....I would like to eliminate the numbers and extract only the text in a
different column...how do i do this?
--
novastar


"T. Valko" wrote:


On Friday, August 21, 2009 1:36 PM Jacob Skaria wrote:

In B1
=SUBSTITUTE(A1,C1,)
In C1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:


On Friday, August 21, 2009 1:41 PM T. Valko wrote:

Let's assume you have this formula to extract the numbers in cell B1:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Enter this formula in C1 to return only the text portion of cell A1:

=TRIM(SUBSTITUTE(A1,B1,""))

--
Biff
Microsoft Excel MVP


On Friday, August 21, 2009 1:44 PM novastar wrote:

Hi Jacob,

This did not work for me. If I have a cell containing "Adam 22", I would
like to separate "adam" in one column and "22" in another column.

With the formula below I get "#N/A" in the cell.

--
novastar


"Jacob Skaria" wrote:


On Friday, August 21, 2009 1:49 PM Jacob Skaria wrote:

You are entering the formula as normal. Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"novastar" wrote:


On Friday, August 21, 2009 1:57 PM novastar wrote:

Hi Jacob,

Thanks....this helps.

Thanks to both Jacob and T.Valko...you helped me fix an issue I have been
struggling with for years. Now I don;t have to go to UNIX and stay in
excel...:-)

Thanks again.
--
novastar


"Jacob Skaria" wrote:


On Friday, August 21, 2009 2:08 PM JP Ronse wrote:

Hi,

You could try 'text to columns' ... and save it as csv.

But if you have to post-process it with unix, why do not making a shell
script that does it all?

I suppose it is a 'text' file like most unix files a
- sed 's/ /,/g' file1 file2 will replace the spaces by ","

Keep in mind that Unix is 7-bit oriented ans Windows/Dos is 8:
dos2unix file file1 ( I think, use man dos2unix in unix environment)

What is the added value of post processing in Unix?

If you do not mind to send me a sample and explaining what your outcome must
be, I will have a look into.



Wkr,

JP


On Friday, August 21, 2009 2:13 PM novastar wrote:

Hi Jacob,

My bad. I was wrong. I did what you said and it works just fine.

Thanks much!
--
novastar


"Jacob Skaria" wrote:


On Friday, August 21, 2009 10:04 PM Ron Rosenfeld wrote:

If the text always comes first, and the number last, then

For text:

=TRIM(LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1 ,2,3,4,5,6,7,8,9,0"))-1))

For the numeric portion (with the number returned as text, which would include
leading zero's):

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4 ,5,6,7,8,9,0")),99)

or, to return the value as numeric:

=--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4, 5,6,7,8,9,0")),99)

--ron