removing part of field

[_Bigred]

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

 

[Bernie Deitrick]

_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,LEN(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

 

[Peo Sjoblom]

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)

 

[Peo Sjoblom]

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]

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

 

[William]

Hi Bigred

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


--
XL2002
Regards

William

(e-mail address removed)

| 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 Sjoblom]

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(",",TRIM(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)

 

[Stephen Dunn]

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.

 

[David Hedberg]

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

 

[Stephen Dunn]

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


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



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

 

[Harlan Grove]

Another option for you. ...
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))))

...

And an alternative to this, also returning the substring to the left of the
first decimal numeral.

=TRIM(LEFT(A1,MIN(SEARCH(ROW(INDIRECT("1:10"))-1,A1&"0123456789"))-1))

If this should also ensure there's a space after the first comma, then

=TRIM(LEFT(SUBSTITUTE(A1,",",", "),
MIN(SEARCH(ROW(INDIRECT("1:10"))-1,A1&"0123456789"))))

Note that if these records are US mailing addresses I believe there are some US
military APO/FPO addresses that could require a different approach.