Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation Formula needed
I am trying to joins 2 text fields in columns a & b.
Column A is a variable field of differring lengths and characters in every row. Column B is the same string in every row of 7 characters long. I need to join A&B where B starts always at position 34 in the string but with blank spaces between them where column A finishes before position 34. this means the total concatenated field length will be 40 which i need to export to another system. So, if column A was the alphabet A-Z i'd want : A-Z (posistions 1 - 26) 7 spaces (positions 27 - 33) Column B (positions 34 - 40). (NB if column A content was 38 long, id want to overwrite the contents with column B from position 34. Remember, field length must always be 40, no more, no less. Ive spent hours trying to do this and thought I was profficient at Excel - it seems not. To confirm, its Excel 2003. Thanks to any would be genius's out there that can help a frustrated soul. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation Formula needed
try:
=A1&REPT(" ",34-LEN(A1))&B1 HTH Regards Sebation.G "Gaffnr" ... I am trying to joins 2 text fields in columns a & b. Column A is a variable field of differring lengths and characters in every row. Column B is the same string in every row of 7 characters long. I need to join A&B where B starts always at position 34 in the string but with blank spaces between them where column A finishes before position 34. this means the total concatenated field length will be 40 which i need to export to another system. So, if column A was the alphabet A-Z i'd want : A-Z (posistions 1 - 26) 7 spaces (positions 27 - 33) Column B (positions 34 - 40). (NB if column A content was 38 long, id want to overwrite the contents with column B from position 34. Remember, field length must always be 40, no more, no less. Ive spent hours trying to do this and thought I was profficient at Excel - it seems not. To confirm, its Excel 2003. Thanks to any would be genius's out there that can help a frustrated soul. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation Formula needed
This should work for you in all situations...
=LEFT(A1&REPT(" ",32),32)&" "&B1 Rick "Gaffnr" wrote in message ... I am trying to joins 2 text fields in columns a & b. Column A is a variable field of differring lengths and characters in every row. Column B is the same string in every row of 7 characters long. I need to join A&B where B starts always at position 34 in the string but with blank spaces between them where column A finishes before position 34. this means the total concatenated field length will be 40 which i need to export to another system. So, if column A was the alphabet A-Z i'd want : A-Z (posistions 1 - 26) 7 spaces (positions 27 - 33) Column B (positions 34 - 40). (NB if column A content was 38 long, id want to overwrite the contents with column B from position 34. Remember, field length must always be 40, no more, no less. Ive spent hours trying to do this and thought I was profficient at Excel - it seems not. To confirm, its Excel 2003. Thanks to any would be genius's out there that can help a frustrated soul. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation Formula needed
Sorry, I misread your posting to say you always wanted a separating space in
front of B1. Try this instead... =LEFT(A1&REPT(" ",33),33)&B1 It will do what you asked for and, as a bonus, is shorter than my original formula. Rick "Rick Rothstein (MVP - VB)" wrote in message ... This should work for you in all situations... =LEFT(A1&REPT(" ",32),32)&" "&B1 Rick "Gaffnr" wrote in message ... I am trying to joins 2 text fields in columns a & b. Column A is a variable field of differring lengths and characters in every row. Column B is the same string in every row of 7 characters long. I need to join A&B where B starts always at position 34 in the string but with blank spaces between them where column A finishes before position 34. this means the total concatenated field length will be 40 which i need to export to another system. So, if column A was the alphabet A-Z i'd want : A-Z (posistions 1 - 26) 7 spaces (positions 27 - 33) Column B (positions 34 - 40). (NB if column A content was 38 long, id want to overwrite the contents with column B from position 34. Remember, field length must always be 40, no more, no less. Ive spent hours trying to do this and thought I was profficient at Excel - it seems not. To confirm, its Excel 2003. Thanks to any would be genius's out there that can help a frustrated soul. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation Formula needed
to all of you that replied - thats wonderful - thanks so much
-- Rob Gaffney |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help needed | Excel Discussion (Misc queries) | |||
Formula Needed | Excel Discussion (Misc queries) | |||
formula to apply concatenation to successive rows of data | Excel Worksheet Functions | |||
Formula help needed | Excel Worksheet Functions | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) |