ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   left function (https://www.excelbanter.com/excel-worksheet-functions/128507-left-function.html)

ah

left function
 
Hi;

Can anyone advice me on how to :
extract all the wording in a cell except the numeric value, for example:
Example:
For ABCDDEFG 1234------- I want it to appear as ABCDEFG for me only.
For ABC198765------------- I want it to appear as ABC for me only

Previously I'm using the left function as follows:
=LEFT(INDIRECT("Sheet1!A"&ROW()),8)

However, for the above formula to work, I need to know the exact number of
characters that I'm going to extract. Please advice whether there is any
other alternative solution for this. For your information, the numeric value
will always be placed at the back.

Bob Phillips

left function
 
=MID(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1: A"&LEN(A1))),1))),
255,ROW(INDIRECT("A1:A"&LEN(A1))))),99)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ah" wrote in message
...
Hi;

Can anyone advice me on how to :
extract all the wording in a cell except the numeric value, for example:
Example:
For ABCDDEFG 1234------- I want it to appear as ABCDEFG for me only.
For ABC198765------------- I want it to appear as ABC for me only

Previously I'm using the left function as follows:
=LEFT(INDIRECT("Sheet1!A"&ROW()),8)

However, for the above formula to work, I need to know the exact number of
characters that I'm going to extract. Please advice whether there is any
other alternative solution for this. For your information, the numeric

value
will always be placed at the back.




Sean

left function
 
I have the exact opposite requirement i.e. to remove all the alpha
values and just leave the numeric, although mine is slightly easier in
that its a Web query that pulls some stock prices, thus I see a tail
"p" which I don't want



On Jan 31, 10:34 am, "Bob Phillips" wrote:
=MID(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1: A"&LEN(A1))),1))),
255,ROW(INDIRECT("A1:A"&LEN(A1))))),99)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ah" wrote in message

...



Hi;


Can anyone advice me on how to :
extract all the wording in a cell except the numeric value, for example:
Example:
For ABCDDEFG 1234------- I want it to appear as ABCDEFG for me only.
For ABC198765------------- I want it to appear as ABC for me only


Previously I'm using the left function as follows:
=LEFT(INDIRECT("Sheet1!A"&ROW()),8)


However, for the above formula to work, I need to know the exact number of
characters that I'm going to extract. Please advice whether there is any
other alternative solution for this. For your information, the numeric

value
will always be placed at the back.- Hide quoted text -


- Show quoted text -




Sean

left function
 
Got the formula below from this NG to extract only numeric values,
don't understand it but it works.

=(MID('Prices'!AC2,MATCH(FALSE,ISERROR(1*MID('Pric es'!
AC2,ROW(INDIRECT("1:"&LEN('Prices'!AC2))),1)),0),L EN('Prices'!AC2)-
SUM(1*ISERROR(1*MID('Prices'!AC2,ROW(INDIRECT("1:" &LEN('Prices'!AC2))),
1))))*1)/100


On Jan 31, 11:01 am, "Sean" wrote:
I have the exact opposite requirement i.e. to remove all the alpha
values and just leave the numeric, although mine is slightly easier in
that its a Web query that pulls some stock prices, thus I see a tail
"p" which I don't want

On Jan 31, 10:34 am, "Bob Phillips" wrote:



=MID(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1: A"&LEN(A1))),1))),
255,ROW(INDIRECT("A1:A"&LEN(A1))))),99)


which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.


--
HTH


Bob Phillips


(there's no email, no snail mail, but somewhere should be gmail in my addy)


"ah" wrote in message


...


Hi;


Can anyone advice me on how to :
extract all the wording in a cell except the numeric value, for example:
Example:
For ABCDDEFG 1234------- I want it to appear as ABCDEFG for me only.
For ABC198765------------- I want it to appear as ABC for me only


Previously I'm using the left function as follows:
=LEFT(INDIRECT("Sheet1!A"&ROW()),8)


However, for the above formula to work, I need to know the exact number of
characters that I'm going to extract. Please advice whether there is any
other alternative solution for this. For your information, the numeric

value
will always be placed at the back.- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -




Bob Phillips

left function
 
Actually mine was exactly that, left the numeric part.

The OP should therefore use

=LEFT(A1,FIND(" ",A1)-1)

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sean" wrote in message
oups.com...
Got the formula below from this NG to extract only numeric values,
don't understand it but it works.

=(MID('Prices'!AC2,MATCH(FALSE,ISERROR(1*MID('Pric es'!
AC2,ROW(INDIRECT("1:"&LEN('Prices'!AC2))),1)),0),L EN('Prices'!AC2)-
SUM(1*ISERROR(1*MID('Prices'!AC2,ROW(INDIRECT("1:" &LEN('Prices'!AC2))),
1))))*1)/100


On Jan 31, 11:01 am, "Sean" wrote:
I have the exact opposite requirement i.e. to remove all the alpha
values and just leave the numeric, although mine is slightly easier in
that its a Web query that pulls some stock prices, thus I see a tail
"p" which I don't want

On Jan 31, 10:34 am, "Bob Phillips" wrote:



=MID(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1: A"&LEN(A1))),1))),
255,ROW(INDIRECT("A1:A"&LEN(A1))))),99)


which is an array formula, it should be committed with

Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly

brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.


--
HTH


Bob Phillips


(there's no email, no snail mail, but somewhere should be gmail in my

addy)

"ah" wrote in message


...


Hi;


Can anyone advice me on how to :
extract all the wording in a cell except the numeric value, for

example:
Example:
For ABCDDEFG 1234------- I want it to appear as ABCDEFG for me

only.
For ABC198765------------- I want it to appear as ABC for me only


Previously I'm using the left function as follows:
=LEFT(INDIRECT("Sheet1!A"&ROW()),8)


However, for the above formula to work, I need to know the exact

number of
characters that I'm going to extract. Please advice whether there is

any
other alternative solution for this. For your information, the

numeric
value
will always be placed at the back.- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -







All times are GMT +1. The time now is 01:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com