Trimming text from the end of cell contents.

[Colin Hayes]

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

 

[Eduardo]

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

 

[Rick Rothstein]

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))

 

[Shane Devenshire]

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.

 

[Colin Hayes]

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.

 

[Sean Timmons]

Original Poster. that's you...

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.

 

[Rick Rothstein]

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.

 

[Harlan Grove]

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?

 

[Colin Hayes]

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