How to replace/delete all charaters that appear after '/'

[jnathan]

I have a spreadsheet showing variable strings however I want to remove all character that appear after / e.g
'Virgin Islands/123/7 H %' should appear as just 'Virgin Islands'.

The strings and the characters after the '/' are variable in length so the replace function using '/????' will not work for everything.

Does anyone have a solution for this? Any help will be greatly appreciated!

Many thanks

Justin

 

[VoG]

Try

=LEFT(A1,FIND("/",A1)-1)

 

[shyy]

If the format is the same where it begins with a ' and ends with a '

Using the formula VoG used but add in the RIGHT so it shows the '

=LEFT(A13,FIND("/",A13)-1)&RIGHT(A13,1)

 

[shyy]

When I first read the OP I thought you wanted to remove everything within the /'s

=LEFT(A13,SEARCH("/",A13,1)-1)&" "&RIGHT(A13,(LEN(A13)-FIND("/",A13,FIND("/",A13)+2)))

 

[jnathan]

Many thanks - this worked best for what I needed!!!

Try

=LEFT(A1,FIND("/",A1)-1)

 

[Rick Rothstein]

The strings and the characters after the '/' are variable in length so the replace function using '/????' will not work for everything.

If you still wanted to use the Replace dialog box (instead of a formula solution), just use '/*' (that is, a slash followed by an asterisk) instead of '/????' in the "Find what" field (with the "Replace with" field being empty)... the asterisk is a wildcard for "zero or more characters". Also, make sure to click the "Options>>" button and make sure "Match entire cell contents" checkbox is not checked.

 

[shyy]

It finds the first / and second / and takes whats left and what is right and combines both of them

Since I know the character position of the first / and second /

Breaking it down its really 3 formulas combined

=LEFT(A13,SEARCH("/",A13,1)-1)
=&" "&
=RIGHT(A13,(LEN(A13)-FIND("/",A13,FIND("/",A13)+2)))


This only works if there are 2 "/"