How do I delete everything after a / in a column?

[Guest]

The first half of my cell is unneccesary, then comes a slash ( / ) and I need
everything after that

 

[Rick Rothstein \(MVP - VB\)]

The first half of my cell is unneccesary, then comes a slash ( / )

and I need everything after that

Your Subject says the opposite of your message. I'll assume your message is
accurate. Assuming A1 is the cell containing your text...

=MID(A8,1+IF(ISERR(FIND("/",A8)),"0",FIND("/",A8)),1024)

The above formula assumes there will only be a maximum of one slash
character. If there is more than one, all characters after the first one
will be returned. (If there is no slash character, the entire text will be
returned.)

Rick

 

[Guest]

Assuming source data in A1 down
In B1: =MID(A1,SEARCH("/",A1)+1,99)
Copy down. Adapt the arbitrary "99" to suit

 

[Guest]

Note that your subject line is misleading. What you describe in your posting
is the opposite. I went with your posting, not your subject line.

---

 

[Peo Sjoblom]

Another way

=MID(A8,FIND("/",A8&"/")+1,1024)

 

[Rick Rothstein \(MVP - VB\)]

The difference between our methods being when no slash character is present
in the cell.

Rick

 

[Peo Sjoblom]

Try it!

Peo

The difference between our methods being when no slash character is
present in the cell.

Rick

 

[Guest]

Try this:

=REPLACE(A1,1,FIND("/",A1),"")

 

[Gord Dibben]

Data>Text to Columns>Delimited by Other. Enter your slash(/) then Next>Do not
Import the first part.


Gord Dibben MS Excel MVP

 

[Dave Thomas]

This works too and returns everything after the "/" not just a maximum of
1024 characters
Like the others it returns everything after the first slash found.

=RIGHT(A1,MAX(LEN(A1)-FIND("/",A1&"/"),0))

 

[Rick Rothstein \(MVP - VB\)]

I did... if there is no slash in the in the text, yours returns the empty
string and mine returns the full text in the cell.

Rick

 

[Dave Peterson]

One more.

If you want to keep everything before the /
Select the column
Edit|Replace
what: /*
with: (leave blank)
replace all

If you want to keep everything after the /
Select the column
Edit|Replace
what: */
with: (leave blank)
replace all

 

[Peo Sjoblom]

Only the OP can tell what he wants, I actually thought that you meant that
mine produced a value error..

 

[Dave Thomas]

Here's yet another solution:

This one is not limited to 1024 characters. It returns everything.
If no "/" is found it returns the empty string. It could be modified to
return everything if no slash is found.
Like the others it returns everything after the first slash found.

=RIGHT(A1,MAX(LEN(A1)-FIND("/",A1&"/"),0))

Dave

 

[Harlan Grove]

Here's yet another solution:

This one is not limited to 1024 characters. It returns everything.
If no "/" is found it returns the empty string. It could be modified to
return everything if no slash is found.
Like the others it returns everything after the first slash found.

=RIGHT(A1,MAX(LEN(A1)-FIND("/",A1&"/"),0))

....

It may be another alternative, but it's suboptimal compared to Teethless
mama's [adapted]

=REPLACE(A1,1,FIND("/",A1&"/"),"")

And the MID formula others have mentioned could be adapted for arbitrary
number of characters up to max string size as

MID(A1,FIND("/",A1&"/")+1,32768)

As for returning the entire string if it contains no /,

=MID(A1,1+COUNTIF(A1,"*/*")*FIND("/",A1&"/"),32768)

 

[Dave Thomas]

Hardcoding numbers like 1024, 32768 in formulas is asking for trouble.
What happens if MS changes the maximum cell length to 65536 in the next
release?

Here's yet another solution:

This one is not limited to 1024 characters. It returns everything.
If no "/" is found it returns the empty string. It could be modified to
return everything if no slash is found.
Like the others it returns everything after the first slash found.

=RIGHT(A1,MAX(LEN(A1)-FIND("/",A1&"/"),0))

...

It may be another alternative, but it's suboptimal compared to Teethless
mama's [adapted]

=REPLACE(A1,1,FIND("/",A1&"/"),"")

And the MID formula others have mentioned could be adapted for arbitrary
number of characters up to max string size as

MID(A1,FIND("/",A1&"/")+1,32768)

As for returning the entire string if it contains no /,

=MID(A1,1+COUNTIF(A1,"*/*")*FIND("/",A1&"/"),32768)

 

[Harlan Grove]

Hardcoding numbers like 1024, 32768 in formulas is asking for trouble.
What happens if MS changes the maximum cell length to 65536 in the next
release?

Using more function calls than necessary ensures slower recalc than
necessary as well as producing unnecessary complexity.

If you want to avoid hardcoding numbers except for obvious ones like 1 or 0,
better to use

=REPLACE(A1,1,FIND("/",A1&"/"),"")

to return "" or

=REPLACE(A1,1,COUNTIF(A1,"*/*")*FIND("/",A1&"/"),"")

to return A1.

But it's still possible to use MID, avoid hardcoded big numbers, and use
fewer function calls that formulas involving RIGHT require.

=MID(A1,1+FIND("/",A1&"/"),LEN(A1))

to return "", or

=MID(A1,1+COUNTIF(A1,"*/*")*FIND("/",A1&"/"),LEN(A1))

to return A1. Or do you have a formula using RIGHT that requires 4 or fewer
function calls to return A1 if it contains no / ?

Using large numbers as 3rd argument to MID reflects a trade-off between
speed of recalc (avoid a function call) in favor of generality in extremely
rare cases (the large number isn't large enough). When improving recalc
speed is essential, using constants in place of function calls when that
would handle 99.9999% of cases is usually acceptable.

 

[Harlan Grove]

=REPLACE(A1,1,COUNTIF(A1,"*/*")*FIND("/",A1&"/"),"")

to return A1.

....

Might as well avoid a function call.

=REPLACE(A1&"/"&A1,1,FIND("/",A1&"/"),"")

not that the first concatenation is instantaneous. This would be a minor
time-storage trade-off.