S
su chen
does anybody know how to separate a cell like 128b into
number (128) and text (b)?
Thanks!
number (128) and text (b)?
Thanks!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
N
Norman Harker
Hi su chen!
If they are all in the form of three digits and a letter, then use
Data > Text to Columns
If they are varying numbers of digits and a single letter then:
To strip out the number use:
=--LEFT(A1,LEN(A1)-1)
To strip off the letter use:
=RIGHT(A1,1)
But for other possibilities, post back and give more examples.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
If they are all in the form of three digits and a letter, then use
Data > Text to Columns
If they are varying numbers of digits and a single letter then:
To strip out the number use:
=--LEFT(A1,LEN(A1)-1)
To strip off the letter use:
=RIGHT(A1,1)
But for other possibilities, post back and give more examples.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
D
David McRitchie
Sorting, Some notes on sorting in Excel
Sorting mixed numbers and alpha, numeric prefix (#mixed)
Sorting product code with alpha prefix and numeric suffix (#pcdigits)
http://www.mvps.org/dmcritchie/excel/sorting.htm#pcdigits
Sorting mixed numbers and alpha, numeric prefix (#mixed)
Sorting product code with alpha prefix and numeric suffix (#pcdigits)
http://www.mvps.org/dmcritchie/excel/sorting.htm#pcdigits
D
Dennis
Norm,
What is the significance for =--Left()?
That is the first time that I saw that systax.
TIA Dennis
What is the significance for =--Left()?
That is the first time that I saw that systax.
TIA Dennis
N
Norman Harker
Hi Dennis!
The double negative forces what would otherwise be a text number to be
a number.
In the example:
=LEFT(A1,LEN(A1)-1)
Returns the number but as text.
We could use:
=VALUE(LEFT(A1,LEN(A1)-1))
That would convert the text number to a number
The double number has the same effect as multiplying by -1 twice.
It's more efficient than making the additional function call but in
this case I think that the better approach might have been to use
VALUE. I think that with modern processor speeds we too often get hung
up about efficiency. With a large workbook with lots of calculations
involving arrays and iteration, it's different.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
The double negative forces what would otherwise be a text number to be
a number.
In the example:
=LEFT(A1,LEN(A1)-1)
Returns the number but as text.
We could use:
=VALUE(LEFT(A1,LEN(A1)-1))
That would convert the text number to a number
The double number has the same effect as multiplying by -1 twice.
It's more efficient than making the additional function call but in
this case I think that the better approach might have been to use
VALUE. I think that with modern processor speeds we too often get hung
up about efficiency. With a large workbook with lots of calculations
involving arrays and iteration, it's different.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
D
Dennis
Norm,
Thanks!
Thanks!
-----Original Message-----
Hi Dennis!
The double negative forces what would otherwise be a text number to be
a number.
In the example:
=LEFT(A1,LEN(A1)-1)
Returns the number but as text.
We could use:
=VALUE(LEFT(A1,LEN(A1)-1))
That would convert the text number to a number
The double number has the same effect as multiplying by - 1 twice.
It's more efficient than making the additional function call but in
this case I think that the better approach might have been to use
VALUE. I think that with modern processor speeds we too often get hung
up about efficiency. With a large workbook with lots of calculations
involving arrays and iteration, it's different.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
.