If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenate or "&" problem
I have tried to concatenate or use the "&" in my database with no luck so
far. I get either the #VALUE! or #NAME? result. I have addresses that are separated into columns by number, direction, street, and suite. I want to combine the numbers and text onto one line, in one column. I've tried reformatting everything into text or numbers, with no luck. I have done this in previous databases before, with no problems. Hopefully someone can help! |
#2
|
|||
|
|||
Concatenate or "&" problem
What formulas? What input values?
-- Kind regards, Niek Otten "Dino" wrote in message ... I have tried to concatenate or use the "&" in my database with no luck so far. I get either the #VALUE! or #NAME? result. I have addresses that are separated into columns by number, direction, street, and suite. I want to combine the numbers and text onto one line, in one column. I've tried reformatting everything into text or numbers, with no luck. I have done this in previous databases before, with no problems. Hopefully someone can help! |
#3
|
|||
|
|||
Concatenate or "&" problem
Hi there,
I've used the "&" function quite extensively for various projects, so I might be able to help. I do know that any cells that you want to use must be formatted as "general" or as "text". Any number formatting will not work and will generate an error message. Let's assume your number is in column A, street name in column B and street type in column C A2 B2 C2 1234 Test Street The formula to combine the 3 would be =A2&" "&B2&" "&C2 giving you the result of: 1234 Test Street If this is already what you are doing, then it might be a problem with the formatting. I've had trouble sometimes with Excel not really changing numbers over to general format, once they are numbers, it can be hard to change. Hope this helps, if not, we might need more specifics. (PS, you can also do modified "If, Then, Else" statements within the & function. They work quite nicely to be sure you don't have double spaces, say between the number and street name if there is no direction.) C. Cunningham "Dino" wrote: I have tried to concatenate or use the "&" in my database with no luck so far. I get either the #VALUE! or #NAME? result. I have addresses that are separated into columns by number, direction, street, and suite. I want to combine the numbers and text onto one line, in one column. I've tried reformatting everything into text or numbers, with no luck. I have done this in previous databases before, with no problems. Hopefully someone can help! |
#4
|
|||
|
|||
Concatenate or "&" problem
For example, if I have the address 123 N Main St in four separate cells, I
used the formula =A1&" "&B1&" "&C1&" "&D1 to get it all in one cell, but I always get the same error code of #VALUE! I have tried to format everything as text, with no luck. They all started out as general formatting. I know it's a formatting problem, because if I just try to combine the last three cells it works fine. When I try to add the numbered cell to the formula, it won't work. I've tried to re-format, copy the cells over as text, everything I can think of. "Niek Otten" wrote: What formulas? What input values? -- Kind regards, Niek Otten "Dino" wrote in message ... I have tried to concatenate or use the "&" in my database with no luck so far. I get either the #VALUE! or #NAME? result. I have addresses that are separated into columns by number, direction, street, and suite. I want to combine the numbers and text onto one line, in one column. I've tried reformatting everything into text or numbers, with no luck. I have done this in previous databases before, with no problems. Hopefully someone can help! |
#5
|
|||
|
|||
Concatenate or "&" problem
For example, if I have the address 123 N Main St in four separate cells, I
used the formula =A1&" "&B1&" "&C1&" "&D1 to get it all in one cell, but I always get the same error code of #VALUE! I have tried to format everything as text, with no luck. They all started out as general formatting. I know it's a formatting problem, because if I just try to combine the last three cells it works fine. When I try to add the numbered cell to the formula, it won't work. I've tried to re-format, copy the cells over as text, everything I can think of. I have done this before in other spreadsheets with no problem. Dino "C. Cunningham" wrote: Hi there, I've used the "&" function quite extensively for various projects, so I might be able to help. I do know that any cells that you want to use must be formatted as "general" or as "text". Any number formatting will not work and will generate an error message. Let's assume your number is in column A, street name in column B and street type in column C A2 B2 C2 1234 Test Street The formula to combine the 3 would be =A2&" "&B2&" "&C2 giving you the result of: 1234 Test Street If this is already what you are doing, then it might be a problem with the formatting. I've had trouble sometimes with Excel not really changing numbers over to general format, once they are numbers, it can be hard to change. Hope this helps, if not, we might need more specifics. (PS, you can also do modified "If, Then, Else" statements within the & function. They work quite nicely to be sure you don't have double spaces, say between the number and street name if there is no direction.) C. Cunningham "Dino" wrote: I have tried to concatenate or use the "&" in my database with no luck so far. I get either the #VALUE! or #NAME? result. I have addresses that are separated into columns by number, direction, street, and suite. I want to combine the numbers and text onto one line, in one column. I've tried reformatting everything into text or numbers, with no luck. I have done this in previous databases before, with no problems. Hopefully someone can help! |
#6
|
|||
|
|||
Concatenate or "&" problem
I can't see anything wrong with your formula. Start with just =A1. If that
does not return an error, try =A1&" ". etc. -- Kind regards, Niek Otten "Dino" wrote in message ... For example, if I have the address 123 N Main St in four separate cells, I used the formula =A1&" "&B1&" "&C1&" "&D1 to get it all in one cell, but I always get the same error code of #VALUE! I have tried to format everything as text, with no luck. They all started out as general formatting. I know it's a formatting problem, because if I just try to combine the last three cells it works fine. When I try to add the numbered cell to the formula, it won't work. I've tried to re-format, copy the cells over as text, everything I can think of. "Niek Otten" wrote: What formulas? What input values? -- Kind regards, Niek Otten "Dino" wrote in message ... I have tried to concatenate or use the "&" in my database with no luck so far. I get either the #VALUE! or #NAME? result. I have addresses that are separated into columns by number, direction, street, and suite. I want to combine the numbers and text onto one line, in one column. I've tried reformatting everything into text or numbers, with no luck. I have done this in previous databases before, with no problems. Hopefully someone can help! |
#7
|
|||
|
|||
Concatenate or "&" problem
Like Niek, I can't find anything wrong either, and I can't get it to
duplicate the error code. I've even tried formatting the number 123 as a number, as currency, as a date, etc, and it is still working fine for me. Must be one of those computer glitches or something. The only other thing I could suggest to try is to copy all the info in your spreadsheet, and paste it into a brand new excel document as text only, and then manually input your formula again. Sometimes that has worked for me when I've had an unexplainable glitch. Hope you get it worked out. (PS, if you don't have all four columns filled in for every record, you can use the following formula: =A1&(IF(B1""," ",""))&B1&(IF(C1""," ",""))&C1&(IF(D1""," ",""))&D1 to erase the double spacing between words.) "Dino" wrote: For example, if I have the address 123 N Main St in four separate cells, I used the formula =A1&" "&B1&" "&C1&" "&D1 to get it all in one cell, but I always get the same error code of #VALUE! I have tried to format everything as text, with no luck. They all started out as general formatting. I know it's a formatting problem, because if I just try to combine the last three cells it works fine. When I try to add the numbered cell to the formula, it won't work. I've tried to re-format, copy the cells over as text, everything I can think of. I have done this before in other spreadsheets with no problem. Dino "C. Cunningham" wrote: Hi there, I've used the "&" function quite extensively for various projects, so I might be able to help. I do know that any cells that you want to use must be formatted as "general" or as "text". Any number formatting will not work and will generate an error message. Let's assume your number is in column A, street name in column B and street type in column C A2 B2 C2 1234 Test Street The formula to combine the 3 would be =A2&" "&B2&" "&C2 giving you the result of: 1234 Test Street If this is already what you are doing, then it might be a problem with the formatting. I've had trouble sometimes with Excel not really changing numbers over to general format, once they are numbers, it can be hard to change. Hope this helps, if not, we might need more specifics. (PS, you can also do modified "If, Then, Else" statements within the & function. They work quite nicely to be sure you don't have double spaces, say between the number and street name if there is no direction.) C. Cunningham "Dino" wrote: I have tried to concatenate or use the "&" in my database with no luck so far. I get either the #VALUE! or #NAME? result. I have addresses that are separated into columns by number, direction, street, and suite. I want to combine the numbers and text onto one line, in one column. I've tried reformatting everything into text or numbers, with no luck. I have done this in previous databases before, with no problems. Hopefully someone can help! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Office XP Update Problem | Bill | General Discussions | 0 | December 1st, 2004 06:11 PM |
Problem with display of header in Word 2003 | Padser | Page Layout | 0 | October 8th, 2004 07:47 AM |
Reinstalling OE... | KAR | Outlook Express | 24 | August 21st, 2004 06:52 PM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |
word error | mac | General Discussions | 1 | May 6th, 2004 08:14 AM |