Flash Fill with No Flash fill

Could you use Find and Replace for this?

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Also, I dont see how flash fill would help with this anyway?

For the state abbreviation, you could use this:

=RIGHT(A2,2)

You would then just need to manually delete the ones that aren't state codes.

Ali, that will work for many, but some have a trailing . which will throw things off

Try this instead...
=TRIM(RIGHT(SUBSTITUTE(A2,".",""),3))

However, this will ONLY work if the last 2 letters are the state. It will not work if the string starts with state, or the last 2 letters are (for some reason) not the state

Originally Posted by bgrish3

This would work for the ones that dont have periods or commas, and my spreadsheet could be a couple thousand rows long, so im trying to avoid that.

oops missed those...
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,".",""),",",""),3))

Originally Posted by JohnTopley

Try

=RIGHT(SUBSTITUTE(SUBSTITUTE(A2,",",""),".",""),2)

Or even:

=UPPER(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,",",""),".",""),2))

which will force upper case letters.

Perhaps if you give us a more representative sample of the data you have?

Well, then in that case, based on the sample, what you have been offered will work for the vast majority of entries. Is it really so hard to go through the list afterwards and tidy up the ones it doesn't work for? Looks to be about 2% of the total, based on the sample you've given us.

Originally Posted by bgrish3

Yes those both work for the ones that actually have states listed, but not for the ones that dont. eg: A34

I dont think this will work without flash fill

Ah..disappointment after disappointment. I think the problem is that you convinced yourself that FashFill would do some kind of miracle but... it didn't happen. I do have FlashFill, and I would like to assure you that it did exactly the same thing that formulas would do. In row 34 formula (and FlashFill) return "Hills" as there is no state in the cell. What would you like there to be?

Originally Posted by bgrish3

I guess i will just have to do it manually. ... If I have to, then I will. I was coming here to see if there was a solution not for you to tell me how easy/hard something is for me..

You have been offered a solution that works for the vast majority of your entries. Are you saying you would rather do the whole lot manually rather than just the small number that the formula can't handle? The solution might not be perfect, but it will still save you a lot of time. I think you are being rather churlish when people have given freely of their own free time to help you out.

Originally Posted by bgrish3

AlKey well thanks for trying. I guess i will just have to do it manually.

You never answered my question. What result would you like to see for the rows that do not have state code? Would you prefer formula to return a blank cell?