Data Visability

[Bandogge]

Okay, I exported contacts from a CRM database. In one of the columns it reads

Phone No.:888-888-8888

When I go to any cells in this column all that appears in the formula bar above is Phone No.:

For some reason the number is not there, nor can I separate/isolate the actual number from the words Phone No.: so that I can remap the field to another CRM.

 

[Tom Urtis]

Duplicate of
http://www.mrexcel.com/forum/showthread.php?t=552559

 

[Ruddles]

When you left-click on the cell and press F2, what happens?

 

[Bandogge]

Ruddles,

The number appears below the words, almost as if someone pressed enter or return on a typewriter. But they are in the same cell, just above and below each other.

 

[Ruddles]

The cell has multiple lines of text separated by linefeed characters. You can edit them out or change them to commas.

The formula bar only displays the first line of text in a cell.

 

[ChrisM]

In 2007+ you can hit the drop down arrow to the far right of the formula bar, it may expand and show the missing data.

You can highlight your column of numbers and run a text-to-columns on it. Choose "space" as the delimiter, maybe check the treat consecutive delimiters as one button.

See if that works.

 

[Bandogge]

Chris,

The formula bar shows Phone.:

when you click the down arrow it shows 888-888-8888 just the number, as if they were in two different columns, but they aren't, they are in the same cell almost as if someone separated them by pressing enter inside the cell. I have a column of 800 numbers that need to be separated from the characters "Phone.:"

When I use any form of text to columns, the number just disappears. I wish this forum had an upload feature, or even a cut and paste feature. I've also tried size as a separator, doesn't work,

To all, I feel like an idiot, I know excel fairly well, for years, just never had this happen, I am in 2007 excel

 

[ChrisM]

Can you just use a formula in the next column to pull the 12 right characters?

=Right(A1,12)

 

[Bandogge]

Chris, that worked, using 14 in place of 12, why who knows, so thanks, but on the new column where the number is, if I go to that cell, I get the formula not the number,

Do you think if I map that column/field to a corresponding field in a crm, that it will load the number and not the formula?

I will try it now, but thanks, that helps a ton.

 

[ChrisM]

It should load the number, but you can always just kill the formula by copying and paste-values on top of itself.

Do that first, then check to make sure you don't have some lingering spaces in front of or behind your phone number. Click one of the cells and inspect in the formula bar, are there any extra spaces? If so, use the =Trim(A1) formula to remove them.