Trimming text from the end of cell contents.

C

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
 
E

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
 
R

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

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.
 
C

Colin Hayes

Rick Rothstein said:
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.
 
S

Sean Timmons

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

Colin Hayes said:
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.
 
R

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.
 
H

Harlan Grove

Shane Devenshire said:
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?
 
C

Colin Hayes

Rick Rothstein said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top