removing part of field

  • Thread starter Thread starter _Bigred
  • Start date Start date
B

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



----------------------------------------------------------------------------
 
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))))
Click to expand...
...

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.
 
Back
Top