Novice seeking expert on extracting certain information from messy excel spreadsheet -

so, your saying some of the data isn't in the correct column and you want an easy way to move the data to the correct column. Is this the real data or demo data? I do not see the @ sign in the email addresses.

What you do is to create a column which indicates the non-conforming records.

=ISNUMBER(SEARCH("@",C2))

This is the magic formula. This returns true there is an @ in cell c2. Returns false otherwise. You can search for any string in place of "@"

Excel formula: Cell contains specific text | Exceljet

and if you need to check more than one thing.

=OR(ISNUMBER(SEARCH("gmail.org",C2)),ISNUMBER(SEARCH("gmail.com",C2)))

https://www.techonthenet.com/excel/formulas/or.php

How to proliferate this formula

copy cell command + c with formula in it. say c2

select all cells in the reset of the column. say c3 to c20

paste command + v

Now, turn on filtering.

select on true in the select column.

You can now move over columns of data. You may need to move email address to a blank column before moving to the real column.

You probably want to get rid of hyperlinks. highlight column. command+k click or remove? button in the lower left. [ this is after the fact ]

R

PS> This was rather quick. ask more question if needed.

finally a way to get rid of automatically generating hyperlinks

excel 2011 on yosemite

[ this is before the fact. ]

tools > autoformat

Looks good to me 😐.

The are no quotes around the formula.

You might want to try d2. It's assuming the value is a string. Maybe number etc. don't work.

No spaces in front of the =

see if you can get

=1+2

to work.

You can get a dropbox.com account. Upload your example spreadsheet to your account. Post link to the uploaded file. Be sure to check the link to see if it works in your web browser

.

R

Did find this? something about what symbol to use for a ,

Error Display window "There's a problem with this formula" - Microsoft Community

Please check the advanced setting in Excel. Keep the "Use system separators" checked ( ticked ).

Recently I unchecked this setting. After this ; is taken in place of ,

After reverting the setting back, formulas worked fine.

This setting might be changed purposely or automatically.

thanks to

kforkannan

julianfrombenmore wrote:

Hi - not amazingly well. Got enough order to extract the bulk of what I needed but sadly not 100% of the info. After hours of fiddling I just had to accept that bulk will have to do.

Thanks for asking

I suggest that next time you have a Microsoft Office question that you try seeking help in the Microsoft Help Community. Microsoft Community

How did this go?

R

When you have messy data, it sometimes requires fiddling around for hours.

Some notes that might help you understand what you see:

Cell C6-The content of that cell extends into the next two columns. Excel will show the full cell contents when adjoining columns do not contain any data. It doesn't mean the data spans all of those columns. You can see that there isn't a cell dividing line inside the text that spans the columns.

Cell A8-That looks like it should be part of the Message, the rest of it appears to be in B8. You would have to cut "hi" from A8, paste it in front of the message in B8, then move all columns to the right by one column.

Cell A14:B14-This appears to be Cell A14 spanning across both cells, but it isn't. There is a dividing line between the cells. The Message data was split in two and needs to be combined. All cells then need to shift right.

Some things you can do to "see" what is where, select the whole grid and click the Wrap Text button. That will cause it to not stretch the data across cells, but make the line "wrap" within the cell, expanding the cell height to fit the contents.

Sometimes that is how it goes. Post if you have any further questions.

R