| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging info from 5 cells to 1
I have a number 21-/A/-525-/E/-0001 the / represent the cells, I want it in
one cell to look like 21-A-525-E-0001. We I concatenate or use & it removes the dashes and turns 0001 into 1. Does anyone have any suggestions? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging info from 5 cells to 1
Stupid suggestion but have you tried formatting the cell as "Text"? I don't know if this will work - just a thought Good luck Tony -- y_not ------------------------------------------------------------------------ y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947 View this thread: http://www.excelforum.com/showthread...hreadid=525399 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging info from 5 cells to 1
Yes I tried that but when I do it removes the dashes and the o's
"y_not" wrote: Stupid suggestion but have you tried formatting the cell as "Text"? I don't know if this will work - just a thought Good luck Tony -- y_not ------------------------------------------------------------------------ y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947 View this thread: http://www.excelforum.com/showthread...hreadid=525399 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging info from 5 cells to 1
If the last set is always 4 chars: =A1&"-"&B1&"-"&C1&"-"&D1&"-"&(REPT(0,4-LEN(E1))&E1) A B C D E FORMULA (RESULT) 1 21 A 525 E 1 21-A-525-E-0001 2 68 B 525 E 21 68-B-525-E-0021 -- jbrackett ------------------------------------------------------------------------ jbrackett's Profile: http://www.excelforum.com/member.php...o&userid=32577 View this thread: http://www.excelforum.com/showthread...hreadid=525399 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging info from 5 cells to 1
Wow that's graet except that the last 4 always show as 4 Zeros
"jbrackett" wrote: If the last set is always 4 chars: =A1&"-"&B1&"-"&C1&"-"&D1&"-"&(REPT(0,4-LEN(E1))&E1) A B C D E FORMULA (RESULT) 1 21 A 525 E 1 21-A-525-E-0001 2 68 B 525 E 21 68-B-525-E-0021 -- jbrackett ------------------------------------------------------------------------ jbrackett's Profile: http://www.excelforum.com/member.php...o&userid=32577 View this thread: http://www.excelforum.com/showthread...hreadid=525399 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging info from 5 cells to 1
maybe:
=A1&"-"&B1&"-"&C1&"-"&D1&"-"&text(e1,"0000") Clare wrote: Wow that's graet except that the last 4 always show as 4 Zeros "jbrackett" wrote: If the last set is always 4 chars: =A1&"-"&B1&"-"&C1&"-"&D1&"-"&(REPT(0,4-LEN(E1))&E1) A B C D E FORMULA (RESULT) 1 21 A 525 E 1 21-A-525-E-0001 2 68 B 525 E 21 68-B-525-E-0021 -- jbrackett ------------------------------------------------------------------------ jbrackett's Profile: http://www.excelforum.com/member.php...o&userid=32577 View this thread: http://www.excelforum.com/showthread...hreadid=525399 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging info from 5 cells to 1
I think your problem is the cells with numbers have some wierd custom
formats. If this is the case you can use a formula like this =TEXT(A1,"#-")&B1&TEXT(C1,"-#-")&D1&TEXT(E1,"-0000") "Clare" wrote: I have a number 21-/A/-525-/E/-0001 the / represent the cells, I want it in one cell to look like 21-A-525-E-0001. We I concatenate or use & it removes the dashes and turns 0001 into 1. Does anyone have any suggestions? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging info from 5 cells to 1
Thanks that worked perfectly
"Sloth" wrote: I think your problem is the cells with numbers have some wierd custom formats. If this is the case you can use a formula like this =TEXT(A1,"#-")&B1&TEXT(C1,"-#-")&D1&TEXT(E1,"-0000") "Clare" wrote: I have a number 21-/A/-525-/E/-0001 the / represent the cells, I want it in one cell to look like 21-A-525-E-0001. We I concatenate or use & it removes the dashes and turns 0001 into 1. Does anyone have any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| comparing a column of cell and then copy info to other cells | Excel Discussion (Misc queries) | |||
| Merging Cells and autofit | Excel Discussion (Misc queries) | |||
| Excel-cannot view info placed in cells | Excel Discussion (Misc queries) | |||
| change info in other cells when i change a number in a drop list? | Excel Discussion (Misc queries) | |||
| WRAP a text line over several columns without merging cells | Excel Worksheet Functions | |||
Hybrid Mode
