ExcelBanter - formula needed

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - formula needed (https://www.excelbanter.com/excel-worksheet-functions/129219-formula-needed.html)

formula needed

Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND(" ",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the numeric and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

Teethless mama

formula needed

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

"ah" wrote:

Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND(" ",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the numeric and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

RagDyeR

formula needed

Se the answer to your post in the misc. group.

Please don't multi-post!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ah" wrote in message
...
Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND(" ",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the numeric and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

formula needed

Hi ;
Thanks for your prompt response. It's really help after I change the formula
to:

=(LEFT(INDIRECT("Sheet1!E"&ROW()),FIND("-"&"",INDIRECT("Sheet1!E"&ROW()))-2))

But, for those cell that are empty, it will returns a result of an error of:
#VALUE! to me. Is there any way for it to remain empty when the cell that I'm
refering to is empty?

Please advice.

Thanks a lot for your kind assistance.

"Teethless mama" wrote:

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

"ah" wrote:

Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND(" ",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the numeric and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

Teethless mama

formula needed

=IF(Sheet1!E1="","",your formula)

"ah" wrote:

Hi ;
Thanks for your prompt response. It's really help after I change the formula
to:

=(LEFT(INDIRECT("Sheet1!E"&ROW()),FIND("-"&"",INDIRECT("Sheet1!E"&ROW()))-2))

But, for those cell that are empty, it will returns a result of an error of:
#VALUE! to me. Is there any way for it to remain empty when the cell that I'm
refering to is empty?

Please advice.

Thanks a lot for your kind assistance.

"Teethless mama" wrote:

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

"ah" wrote:

Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND(" ",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the numeric and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

T. Valko

formula needed

=IF(INDIRECT("Sheet1!E"&ROW())="","",LEFT(INDIRECT ("Sheet1!E"&ROW()),FIND("-",INDIRECT("Sheet1!E"&ROW()))-2))

Biff

"Teethless mama" wrote in message
...
=IF(Sheet1!E1="","",your formula)

"ah" wrote:

Hi ;
Thanks for your prompt response. It's really help after I change the
formula
to:

=(LEFT(INDIRECT("Sheet1!E"&ROW()),FIND("-"&"",INDIRECT("Sheet1!E"&ROW()))-2))

But, for those cell that are empty, it will returns a result of an error
of:
#VALUE! to me. Is there any way for it to remain empty when the cell that
I'm
refering to is empty?

Please advice.

Thanks a lot for your kind assistance.

"Teethless mama" wrote:

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

"ah" wrote:

Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value
appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND("
",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the
numeric and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

formula needed

Hi;

I've changed the formula to the following, but the wording of #VALUE! still
appears to me:

=IF("Sheet1!E"&ROW()="
","",(LEFT(INDIRECT("Sheet1!E"&ROW()),FIND("-",INDIRECT("Sheet1!E"&ROW()))-1)))

Can you help me on this? Thanks a lot!

"Teethless mama" wrote:

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

"ah" wrote:

Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND(" ",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the numeric and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

formula needed

thanks a lot!
It works.

"T. Valko" wrote:

=IF(INDIRECT("Sheet1!E"&ROW())="","",LEFT(INDIRECT ("Sheet1!E"&ROW()),FIND("-",INDIRECT("Sheet1!E"&ROW()))-2))

Biff

"Teethless mama" wrote in message
...
=IF(Sheet1!E1="","",your formula)

"ah" wrote:

Hi ;
Thanks for your prompt response. It's really help after I change the
formula
to:

=(LEFT(INDIRECT("Sheet1!E"&ROW()),FIND("-"&"",INDIRECT("Sheet1!E"&ROW()))-2))

But, for those cell that are empty, it will returns a result of an error
of:
#VALUE! to me. Is there any way for it to remain empty when the cell that
I'm
refering to is empty?

Please advice.

Thanks a lot for your kind assistance.

"Teethless mama" wrote:

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

"ah" wrote:

Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value
appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND("
",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the
numeric and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

T. Valko

formula needed

You're welcome!

Biff

"ah" wrote in message
...
thanks a lot!
It works.

"T. Valko" wrote:

=IF(INDIRECT("Sheet1!E"&ROW())="","",LEFT(INDIRECT ("Sheet1!E"&ROW()),FIND("-",INDIRECT("Sheet1!E"&ROW()))-2))

Biff

"Teethless mama" wrote in
message
...
=IF(Sheet1!E1="","",your formula)

"ah" wrote:

Hi ;
Thanks for your prompt response. It's really help after I change the
formula
to:

=(LEFT(INDIRECT("Sheet1!E"&ROW()),FIND("-"&"",INDIRECT("Sheet1!E"&ROW()))-2))

But, for those cell that are empty, it will returns a result of an
error
of:
#VALUE! to me. Is there any way for it to remain empty when the cell
that
I'm
refering to is empty?

Please advice.

Thanks a lot for your kind assistance.

"Teethless mama" wrote:

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

"ah" wrote:

Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value
appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND("
",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the
numeric and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

David Biddulph

formula needed

You've forgotten an INDIRECT.
Try
=IF(INDIRECT("Sheet1!E"&ROW())="","",(LEFT(INDIREC T("Sheet1!E"&ROW()),FIND("-",INDIRECT("Sheet1!E"&ROW()))-1)))
--
David Biddulph

"ah" wrote in message
...
Hi;

I've changed the formula to the following, but the wording of #VALUE!
still
appears to me:

=IF("Sheet1!E"&ROW()="
","",(LEFT(INDIRECT("Sheet1!E"&ROW()),FIND("-",INDIRECT("Sheet1!E"&ROW()))-1)))

Can you help me on this? Thanks a lot!

"Teethless mama" wrote:

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

"ah" wrote:

Hi;

Can anyone advice me on how to extract the text value only?
The formula that I'm using currently works well if the text value
appears
before the space only:

=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND("
",INDIRECT("Sheet1!A"&ROW()))-1))

My requirements are to appear all the text value, and skip the numeric
and
symbol.
For example:
a) For ABC - 123, it should appears as ABC to me
b) For ABC DEF -098, it should appears as ABC DEF to me.

Thanks for your help in advance.

All times are GMT +1. The time now is 04:12 PM.