Use a formula to delete part of a text string?

[Josh Craig]

Hi I was just wondering if I could use a formula to delete part of text in a
cell after a certain string of text appears within that cell where the number
of characters before the certain text varies for example:

Big Fat Cat-brown-2803
Small dog-brown-705
Large sheep-brown-106
Small cat-brown-1803

I was looking for a formula so that everything after "-brown" and "-brown"
itself is is deleted. I can only think how to do this where there is a
consistent number of characters before or after the phrase I want to delete,
but unfortunately I don't have that luxury.

Any advice would be greatly appreciated!

 

[Stefi]

Say your text is in A2, then insert in B2:
=LEFT(A2,SEARCH("-brown",A2)-1)

Regards,
Stefi

„Josh Craig†ezt írta:

 

[Bondi]

Hi I was just wondering if I could use a formula to delete part of text in a
cell after a certain string of text appears within that cell where the number
of characters before the certain text varies for example:

Big Fat Cat-brown-2803
Small dog-brown-705
Large sheep-brown-106
Small cat-brown-1803

I was looking for a formula so that everything after "-brown" and "-brown"
itself is is deleted. I can only think how to do this where there is a
consistent number of characters before or after the phrase I want to delete,
but unfortunately I don't have that luxury.

Any advice would be greatly appreciated!

Hi,

One way would to use something like this: (With text in A1)

=LEFT(A1,LEN(A1)-SEARCH("-brown",A1,1))

Regards,
Bondi

 

[Bondi]

Hi Again,

Sorry.. disregard all the stuff i just wrote... It is compleat rubish

Stefi's works like a charm thou..

Regards,
Bondi

 

[Biff]

Hi!

Try this:

Select the range of cells in question.
Goto Data>Text to Columns
Select Delimited
Next
Select Other and in the little box to the right enter - (enter a dash)
Next
Click inside the column where it shows all the browns
Then select Do not import column (skip)
Do the same thing for the next column to the right
Finish

Biff