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

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

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

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

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

Assuming source data in A1 down
In B1: =MID(A1,SEARCH("/",A1)+1,99)
Copy down. Adapt the arbitrary "99" to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"henderson" wrote:
The first half of my cell is unneccesary, then comes a slash ( / ) and I need
everything after that

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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Another way

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



--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

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

Rick


"Peo Sjoblom" wrote in message
...
Another way

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



--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

Try it!

Peo


"Rick Rothstein (MVP - VB)" wrote in
message ...
The difference between our methods being when no slash character is
present in the cell.

Rick


"Peo Sjoblom" wrote in message
...
Another way

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



--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

Try this:

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


"henderson" wrote:

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

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

DataText to ColumnsDelimited by Other. Enter your slash(/) then NextDo not
Import the first part.


Gord Dibben MS Excel MVP



On Wed, 18 Jul 2007 14:54:00 -0700, henderson
wrote:

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

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

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


"henderson" wrote in message
...
The first half of my cell is unneccesary, then comes a slash ( / ) and I
need
everything after that

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

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


"Peo Sjoblom" wrote in message
...
Try it!

Peo


"Rick Rothstein (MVP - VB)" wrote in
message ...
The difference between our methods being when no slash character is
present in the cell.

Rick


"Peo Sjoblom" wrote in message
...
Another way

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



--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

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


henderson wrote:

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

--

Dave Peterson

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

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


--

Regards,

Peo Sjoblom

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Peo Sjoblom" wrote in message
...
Try it!

Peo


"Rick Rothstein (MVP - VB)" wrote in
message ...
The difference between our methods being when no slash character is
present in the cell.

Rick


"Peo Sjoblom" wrote in message
...
Another way

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



--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

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

"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

"Dave Thomas" wrote...
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)

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

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?

"Harlan Grove" wrote in message
...
"Dave Thomas" wrote...
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)

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

"Dave Thomas" wrote...
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.

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

"Harlan Grove" wrote...
....
=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.