Moving Conditional data from column to column

jlbecker67

New Member
Joined
Mar 26, 2002
Messages
8
Maybe this is simple, but I am not very good with excel. I want to write a formula to move data to another cell based on certain criteria.
Example: I have a spreadsheet with our county's property tax values with addresses. The addresses didn't split into columns right. Sometimes the street (st) got moved to the wrong column because there wasn't a direction on the address. How do I move all these two letter designations to the right columns without a lot of time?

Thank you very much,
Jerry
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Couple of ways to do this:

Let's say your data is in A1:K1000

Add a column L with a formula: =IF(LEN(G2)=2,"MOVE ME","OK ALREADY"). Copy this formula from L2 to L3:L1000.

In column M, add a sequence number that runs from 1 in Cell M2 to 999 in M1000.

Sort A1:M1000 by column L. You now have all of the "wrong" records in a block at the top of the worksheet. Once they are in a block, you can Cut and paste from G2:G100 to H2:H100.

Then, sort A1:M1000 by column M to get everything back in the right order. Delete your columns L & M.

(The other way is with a VBA macro - the above is simpler, unless you have to do this every day)

Bill

_________________
MrExcel.com Consulting
This message was edited by MrExcel on 2002-03-27 19:47
 
Upvote 0
Hi jlbecker67:
Another variation towards the end of the excellent solution proposed by Bill, after you have got all the addresses corrected, and say your State Column is in the wrong order ...
see sample below:

1name 2street 4state 3city 5zip
Smith 101 Bay MI Detroit 48226
Keller 200 Kelly OH Akron 11111
Baylor 111 Kate MI Troy 48098

The right order would of entries would have been
1name
2street
3city
4state
5zip

At this point, you can sort the affected subset of records, with the option of sorting records LEFT to Right -- (that's why you will notice, I changed the field headers to have 1,2,3,4, and 5 as the first character)

then your sorted subset will look like as:

1name 2street 3city 4state 5zip
Smith 101 Bay Detroit MI 48226
Keller 200 Kelly Akron OH 11111
Baylor 111 Kate Troy MI 48098

HTH
 
Upvote 0
Maybe I didn't explain the problem correctly.
Example:
John Doe.....123.....W.........Main.....St
Jane Doe.....987.....Nowhere...St

How can I do move all the "St" in the wrong column to the right one and then move the street name to the right column? Can I have a formula that finds the two letter designations and moves them to the column to the right? And then have one that finds all data with more than two letters (for the street name) and move it to the column to the right?
Thanks for your help. Sorry I am so ignorant.

Jerry
This message was edited by jlbecker67 on 2002-03-28 06:22
 
Upvote 0
On 2002-03-28 06:19, jlbecker67 wrote:
Maybe I didn't explain the problem correctly.
Example:
John Doe.....123.....W.........Main.....St
Jane Doe.....987.....Nowhere...St

How can I do move all the "St" in the wrong column to the right one and then move the street name to the right column? Can I have a formula that finds the two letter designations and moves them to the column to the right? And then have one that finds all data with more than two letters (for the street name) and move it to the column to the right?
Thanks for your help. Sorry I am so ignorant.

Jerry
This message was edited by jlbecker67 on 2002-03-28 06:22

Hi Jerry:
I believe Bill and I have given you two of several workable solutions. It does need working at it to get a good grasp on things. I don't think I follow what you are asking now. If you post a sample of your worksheet data or email it to me, I will be glad to look at it!
 
Upvote 0
I have gotten everything sorted but it left some leading spaces. I am now trying to figure out how to get rid of them quickly and easily. Any ideas? Thanks for all the help.

Jerry
 
Upvote 0
On 2002-04-01 08:33, jlbecker67 wrote:
I have gotten everything sorted but it left some leading spaces. I am now trying to figure out how to get rid of them quickly and easily. Any ideas? Thanks for all the help.

Jerry
Hi Jerry:
try the trim function ... if there are extra spaces in cell A1, then
=TRIM(A1)
would remove the extra spaces!
This message was edited by Yogi Anand on 2002-04-03 20:39
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top