Hi

I need help with a small problem.

I have a column which has rows each of which end with 'Ref : ' and then
a number.

I'd like to be able to trim all of this from the end of each cell ,
leaving the rest of the cell content intact.

Can someone help?

Grateful for any advice.



Best Wishes

Hi,
assuming your test start in cell A1 in B1 enter

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

if this helps please clikc yes thanks

"Colin Hayes" wrote:


Hi

I need help with a small problem.

I have a column which has rows each of which end with 'Ref : ' and then
a number.

I'd like to be able to trim all of this from the end of each cell ,
leaving the rest of the cell content intact.

Can someone help?

Grateful for any advice.



Best Wishes

You should add the colon after the "Ref" so as to avoid false positives with
words such as "refined", "bereft", and so on. The only problem is to know
where the colon goes... the OP shows a space between the "f" and the
colon... I'm willing to bet that is a typo. Assuming the colon follows the
"f" immediately, the OP should probably use...

=TRIM(LEFT(A1,FIND("Ref:",A1)-1))

--
Rick (MVP - Excel)


"Eduardo" wrote in message
...
Hi,
assuming your test start in cell A1 in B1 enter

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

if this helps please clikc yes thanks

"Colin Hayes" wrote:


Hi

I need help with a small problem.

I have a column which has rows each of which end with 'Ref : ' and then
a number.

I'd like to be able to trim all of this from the end of each cell ,
leaving the rest of the cell content intact.

Can someone help?

Grateful for any advice.



Best Wishes

It would help to see a sample line of data because you might be able to use
the Data, Text to Columns command if we saw a consistant pattern in the data.

Additionally, if Ref : only appears once you could use

=LEFT(A1,FIND(":",A1)-6)

It looks to me as though you have a space between Ref and :. If not change
the -6 to -5.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Colin Hayes" wrote:


Hi

I need help with a small problem.

I have a column which has rows each of which end with 'Ref : ' and then
a number.

I'd like to be able to trim all of this from the end of each cell ,
leaving the rest of the cell content intact.

Can someone help?

Grateful for any advice.



Best Wishes

In article , Rick Rothstein
writes
You should add the colon after the "Ref" so as to avoid false positives with
words such as "refined", "bereft", and so on. The only problem is to know
where the colon goes... the OP shows a space between the "f" and the
colon... I'm willing to bet that is a typo. Assuming the colon follows the
"f" immediately, the OP should probably use...

=TRIM(LEFT(A1,FIND("Ref:",A1)-1))


Hi All

OK thanks for your suggestions - all of which did the trick and solved
my problem. It works perfectly.

Just a FYI - there *is* a space after 'Ref' and before the colon , so I
was able to modify slightly the code to fit.

Not sure what 'OP' means....

^_^


Thanks again.

:-) Original Poster. that's you...

"Colin Hayes" wrote:

In article , Rick Rothstein
writes
You should add the colon after the "Ref" so as to avoid false positives with
words such as "refined", "bereft", and so on. The only problem is to know
where the colon goes... the OP shows a space between the "f" and the
colon... I'm willing to bet that is a typo. Assuming the colon follows the
"f" immediately, the OP should probably use...

=TRIM(LEFT(A1,FIND("Ref:",A1)-1))


Hi All

OK thanks for your suggestions - all of which did the trick and solved
my problem. It works perfectly.

Just a FYI - there *is* a space after 'Ref' and before the colon , so I
was able to modify slightly the code to fit.

Not sure what 'OP' means....

^_^


Thanks again.

Then I presume you are using this...

=TRIM(LEFT(A1,FIND("Ref :",A1)-1))

OP is an abbreviation for "Original Poster"... saves us from having to look
back to see if the OP used a real name or an odd non-name.

--
Rick (MVP - Excel)


"Colin Hayes" wrote in message
...
In article , Rick Rothstein
writes
You should add the colon after the "Ref" so as to avoid false positives
with
words such as "refined", "bereft", and so on. The only problem is to know
where the colon goes... the OP shows a space between the "f" and the
colon... I'm willing to bet that is a typo. Assuming the colon follows the
"f" immediately, the OP should probably use...

=TRIM(LEFT(A1,FIND("Ref:",A1)-1))


Hi All

OK thanks for your suggestions - all of which did the trick and solved my
problem. It works perfectly.

Just a FYI - there *is* a space after 'Ref' and before the colon , so I
was able to modify slightly the code to fit.

Not sure what 'OP' means....

^_^


Thanks again.

Shane Devenshire wrote...
....
Additionally, if Ref : only appears once you could use

=LEFT(A1,FIND(":",A1)-6)

No, if : appears just once, your formula would work. If there were
other, preceding colons, your formula would fubar.

If the OP really does want to remove this stuff, and if there were
only just one instance of 'Ref : #...' in each cell, wouldn't it have
been faster for the OP to replace

Ref : *

with nothing using Edit Replace?

In article , Rick Rothstein
writes
Then I presume you are using this...

=TRIM(LEFT(A1,FIND("Ref :",A1)-1))

OP is an abbreviation for "Original Poster"... saves us from having to look
back to see if the OP used a real name or an odd non-name.



Hi Rick

Yes , that's the one I'm using. It fits the bill perfectly.


Thanks too for acronym clarification.


Best Wishes