removing part of field

I have a excel 2000 file that contains 300-400 records.

The field in question contains data such as:

OSHKOSH, WI 54901
OSHKOSH,WI 54902
OSHKOSH,WI

** Question: Is there a formula that would enable me to remove everything
after WI???

I would like the final results to be:

OSHKOSH, WI

*** If this isn't possible I would like to remove the comma & everything
after it.

TIA,
_Bigred

_Bigred,

If your data always has a 5 digit zip code, then for data starting in A1,
use this in B1

=IF(ISERROR(VALUE(RIGHT(A1,1))),A1,TRIM(LEFT(A1,LE N(A1)-5)))

and copy down to match your data.

If you can also have a 9 digit zip code, like

OSHKOSH, WI 54901-4567

then copy the formula over for another column (to C1), and down again, and
use column C's values.

For either case, if you no longer need the original data, copy and
pastespecial values over the formulas, then delete the original data.

HTH,
Bernie
MS Excel MVP

"_Bigred" wrote in message
...
I have a excel 2000 file that contains 300-400 records.

The field in question contains data such as:

OSHKOSH, WI 54901
OSHKOSH,WI 54902
OSHKOSH,WI

** Question: Is there a formula that would enable me to remove everything
after WI???

I would like the final results to be:

OSHKOSH, WI

*** If this isn't possible I would like to remove the comma & everything
after it.

TIA,
_Bigred

One way

=SUBSTITUTE(TRIM(LEFT(A1,FIND("WI",A1)+1)),","," ")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"_Bigred" wrote in message
...
I have a excel 2000 file that contains 300-400 records.

The field in question contains data such as:

OSHKOSH, WI 54901
OSHKOSH,WI 54902
OSHKOSH,WI

** Question: Is there a formula that would enable me to remove everything
after WI???

I would like the final results to be:

OSHKOSH, WI

*** If this isn't possible I would like to remove the comma & everything
after it.

TIA,
_Bigred

Doh! Talk about being thick, me assuming that there only would be one state
(blush)

=SUBSTITUTE(LEFT(SUBSTITUTE(A1," ",""),FIND(",",SUBSTITUTE(A1,"
",""))+2),","," ")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Bernie Deitrick" deitbe @ consumer dot org wrote in message
...
_Bigred,

If your data always has a 5 digit zip code, then for data starting in A1,
use this in B1

=IF(ISERROR(VALUE(RIGHT(A1,1))),A1,TRIM(LEFT(A1,LE N(A1)-5)))

and copy down to match your data.

If you can also have a 9 digit zip code, like

OSHKOSH, WI 54901-4567

then copy the formula over for another column (to C1), and down again, and
use column C's values.

For either case, if you no longer need the original data, copy and
pastespecial values over the formulas, then delete the original data.

HTH,
Bernie
MS Excel MVP

"_Bigred" wrote in message
...
I have a excel 2000 file that contains 300-400 records.

The field in question contains data such as:

OSHKOSH, WI 54901
OSHKOSH,WI 54902
OSHKOSH,WI

** Question: Is there a formula that would enable me to remove
everything
after WI???

I would like the final results to be:

OSHKOSH, WI

*** If this isn't possible I would like to remove the comma & everything
after it.

TIA,
_Bigred

Peo,

There might be only one state. ;-)

I thought about looking for spaces, too, until I noticed the last two
examples, without a space between the city and state, or if there were city
names like San Diego, etc... Probably a typo, but it seemed safer to look
for trailing numbers. I probably should have trimmed the original as well
to remove trailing spaces first, but only the OP knows how bad his data
actually is....

Bernie

"Peo Sjoblom" wrote in message
...
Doh! Talk about being thick, me assuming that there only would be one
state
(blush)

=SUBSTITUTE(LEFT(SUBSTITUTE(A1," ",""),FIND(",",SUBSTITUTE(A1,"
",""))+2),","," ")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Bernie Deitrick" deitbe @ consumer dot org wrote in message
...
_Bigred,

If your data always has a 5 digit zip code, then for data starting in
A1,
use this in B1

=IF(ISERROR(VALUE(RIGHT(A1,1))),A1,TRIM(LEFT(A1,LE N(A1)-5)))

and copy down to match your data.

If you can also have a 9 digit zip code, like

OSHKOSH, WI 54901-4567

then copy the formula over for another column (to C1), and down again,
and
use column C's values.

For either case, if you no longer need the original data, copy and
pastespecial values over the formulas, then delete the original data.

HTH,
Bernie
MS Excel MVP

"_Bigred" wrote in message
...
I have a excel 2000 file that contains 300-400 records.

The field in question contains data such as:

OSHKOSH, WI 54901
OSHKOSH,WI 54902
OSHKOSH,WI

** Question: Is there a formula that would enable me to remove
everything
after WI???

I would like the final results to be:

OSHKOSH, WI

*** If this isn't possible I would like to remove the comma &
everything
after it.

TIA,
_Bigred

Hi Bigred

1) Select the range in question.
2) From the Excel toolbar, select "EditReplace"
3) In the "Find What" box, enter WI*
4) In the Replace With" box, enter WI
5) Click "OK"


--
XL2002
Regards

William



"_Bigred" wrote in message
...
| I have a excel 2000 file that contains 300-400 records.
|
| The field in question contains data such as:
|
| OSHKOSH, WI 54901
| OSHKOSH,WI 54902
| OSHKOSH,WI
|
| ** Question: Is there a formula that would enable me to remove everything
| after WI???
|
| I would like the final results to be:
|
| OSHKOSH, WI
|
| *** If this isn't possible I would like to remove the comma & everything
| after it.
|
| TIA,
| _Bigred
|
|
|
|
|

Hi Bernie,

since my solution would fail for New York or San Diego

=LEFT(TRIM(A1),FIND(",",TRIM(A1)))&"
"&LEFT(TRIM(SUBSTITUTE(A1,LEFT(TRIM(A1),FIND(",",T RIM(A1))),"")),2)

it will add a space after the comma in case the OP's 2 last lines are for
real

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Bernie Deitrick" deitbe @ consumer dot org wrote in message
...
Peo,

There might be only one state. ;-)

I thought about looking for spaces, too, until I noticed the last two
examples, without a space between the city and state, or if there were
city
names like San Diego, etc... Probably a typo, but it seemed safer to look
for trailing numbers. I probably should have trimmed the original as well
to remove trailing spaces first, but only the OP knows how bad his data
actually is....

Bernie

"Peo Sjoblom" wrote in message
...
Doh! Talk about being thick, me assuming that there only would be one
state
(blush)

=SUBSTITUTE(LEFT(SUBSTITUTE(A1," ",""),FIND(",",SUBSTITUTE(A1,"
",""))+2),","," ")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Bernie Deitrick" deitbe @ consumer dot org wrote in message
...
_Bigred,

If your data always has a 5 digit zip code, then for data starting in
A1,
use this in B1

=IF(ISERROR(VALUE(RIGHT(A1,1))),A1,TRIM(LEFT(A1,LE N(A1)-5)))

and copy down to match your data.

If you can also have a 9 digit zip code, like

OSHKOSH, WI 54901-4567

then copy the formula over for another column (to C1), and down again,
and
use column C's values.

For either case, if you no longer need the original data, copy and
pastespecial values over the formulas, then delete the original data.

HTH,
Bernie
MS Excel MVP

"_Bigred" wrote in message
...
I have a excel 2000 file that contains 300-400 records.

The field in question contains data such as:

OSHKOSH, WI 54901
OSHKOSH,WI 54902
OSHKOSH,WI

** Question: Is there a formula that would enable me to remove
everything
after WI???

I would like the final results to be:

OSHKOSH, WI

*** If this isn't possible I would like to remove the comma &
everything
after it.

TIA,
_Bigred

Another option for you.

If there is always a comma (with or without a space) followed by only two
characters, then ignore this and go with Peo's suggestion.

This will remove everything after and including the first numeric character
in A1. It allows for cases where the comma may not be present, but will
ensure a space after the first comma where it does exist.


=TRIM(LEFT(A1,IF(ISERR(FIND(",",A1)),0,FIND(",",A1 )))&
" "&MID(A1,IF(ISERR(FIND(",",A1)),1,FIND(",",A1) +1),
IF(ISNA(MATCH(0,0*MID(A1,COLUMN(1:1),1),0)),LEN(A1 ),
MATCH(0,0*MID(A1,COLUMN(1:1),1),0)-1)-
IF(ISERR(FIND(",",A1)),0,FIND(",",A1))))


It's an array formula (so hold Ctrl+Shift when you press Enter).

Steve D.


"_Bigred" wrote in message
...
I have a excel 2000 file that contains 300-400 records.

The field in question contains data such as:

OSHKOSH, WI 54901
OSHKOSH,WI 54902
OSHKOSH,WI

** Question: Is there a formula that would enable me to remove everything
after WI???

I would like the final results to be:

OSHKOSH, WI

*** If this isn't possible I would like to remove the comma & everything
after it.

TIA,
_Bigred

Find and replace works for me.
Just find what you want and replace it with null.

Hi David, OP has 300-400 records, each apparently with different numbers to
be removed...


"David Hedberg" wrote in message
...
Find and replace works for me.
Just find what you want and replace it with null.



----------------------------------------------------------------------------
----


Find and replace works for me.
Just find what you want and replace it with null.