+ Reply to Thread
Results 1 to 15 of 15

Concatenate 2 columns

Hybrid View

  1. 05-03-2011, 06:03 PM #1
    sirgeo
    sirgeo is offline
    Registered User
    Join Date
    03-10-2011
    Location
    Austin, texas
    MS-Off Ver
    Excel 2003
    Posts
    26

    Concatenate 2 columns

    Please see attached xls file.

    We need to concatenate Columns E (Route) and F (Segment Number) into Column M (ROUTESEG)

    As you will see in Column L (RouteSegxxx) the tenth decimal place (.0) does not carry over into the solution.

    Column M (ROUTESEG) needs to be to the tenth place (.0)
    i.e. 100-101.0
    100-102.0
    100-103.0
    etc.

    using =CONCATENATE(E2,"-",F2) does not result in (.0)s

    Any help with this would be greatly appreciated!

    this tenth place (.0) designation is a requirement when doing "Join" functions in ArcGIS software.
    Attached Files Attached Files
    Last edited by sirgeo; 05-03-2011 at 06:10 PM.
    Reply With QuoteRegister To Reply
  2. 05-03-2011, 06:15 PM #2
    StephenR
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Complex Concatenation of 2 columns

    But 100-101.0 is nine characters? In any case, perhaps this?

    =TEXT(E2,"00#")&"-"&TEXT(F2,"#.0")
    Reply With QuoteRegister To Reply
  3. 05-03-2011, 06:22 PM #3
    sirgeo
    sirgeo is offline
    Registered User
    Join Date
    03-10-2011
    Location
    Austin, texas
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Complex Concatenation of 2 columns

    Quote Originally Posted by StephenR View Post
    But 100-101.0 is nine characters? In any case, perhaps this?

    =TEXT(E2,"00#")&"-"&TEXT(F2,"#.0")
    Thanks for the quick reply Stephen!
    That almost works, just need the hundredths (.00) to remain where ever they occur.
    i.e. the 2nd row needs to be 002-184.26 not 002-184.3
    not sure what you mean about the nine characters comment, is there a limit in excel or something?

    actually by just adding a zero to your formula : =TEXT(E2,"00#")&"-"&TEXT(F2,"#.00")
    we get the hundredth place we need... i think this will work.
    Last edited by sirgeo; 05-03-2011 at 06:25 PM.
    Reply With QuoteRegister To Reply
  4. 05-03-2011, 06:23 PM #4
    StephenR
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Complex Concatenation of 2 columns

    Try this then:

    =TEXT(E3,"00#")&"-"&TEXT(F3,"#.00")

    Joins and Relates. What fun...
    Reply With QuoteRegister To Reply
  5. 05-03-2011, 06:28 PM #5
    TonyS
    TonyS is offline
    Forum Contributor
    Join Date
    10-08-2006
    Location
    Walnut, CA
    MS-Off Ver
    2003,2010, Office 365
    Posts
    114

    Re: Complex Concatenation of 2 columns

    Hi,
    Your route(Column E) is alphanumeric and the length are varied between cells.
    Something like this?
    Public Sub FormatStuff()
Dim lR As Long
Dim i As Long
lR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lR
    Cells(i, 13) = Cells(i, 5).Text & "-" & Cells(i, 6).Text
Next i
End Sub
    Tony
    Last edited by Leith Ross; 05-03-2011 at 09:46 PM. Reason: Replaced Quote Tags with Code Tags
    Reply With QuoteRegister To Reply
  6. 05-03-2011, 07:24 PM #6
    sirgeo
    sirgeo is offline
    Registered User
    Join Date
    03-10-2011
    Location
    Austin, texas
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Complex Concatenation of 2 columns

    Quote Originally Posted by TonyS View Post
    Hi,
    Your route(Column E) is alphanumeric and the length are varied between cells.
    Something like this?

    Tony
    Thanks Tony, can you please explain how implement this code?
    it's a macro, correct?
    Reply With QuoteRegister To Reply
  7. 05-03-2011, 09:58 PM #7
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Complex Concatenation of 2 columns

    Hello sirgeo,

    I really don't see a need for using a VBA macro for this when a worksheet formula will work. This is the formula I used on the worksheet and it seems to meet your requirements.
    =TEXT(E2, "000") & "-" & TEXT(F2,"#,##0.0#")
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  8. 05-04-2011, 10:03 AM #8
    sirgeo
    sirgeo is offline
    Registered User
    Join Date
    03-10-2011
    Location
    Austin, texas
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Complex Concatenation of 2 columns

    Quote Originally Posted by Leith Ross View Post
    Hello sirgeo,

    I really don't see a need for using a VBA macro for this when a worksheet formula will work. This is the formula I used on the worksheet and it seems to meet your requirements.
    =TEXT(E2, "000") & "-" & TEXT(F2,"#,##0.0#")
    Thanks Leith,

    Yes, that appears to solve the original problem, for the most part, but there is another ArcGIS Join function limitation that needs to be addressed.

    As shown in the attached screen shot, we need to truncate the extra zeros in the xls file (also attached) while leaving 3 decimal places for all other records. ArcGIS is extremely archaic when it comes to dealing with these extra meaningless zeros unfortunately.
    Much thanks for all the help thus far btw!
    Attached Images Attached Images
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 05-03-2011, 06:45 PM #9
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Complex Concatenation of 2 columns

    =if(int(f2)=f2,text(e2,"00#") & "-"& text(f2,"#"),text(e2,"00#") & "-"& text(f2,"#.##"))
    Reply With QuoteRegister To Reply
  10. 05-04-2011, 11:28 AM #10
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Complex Concatenation of 2 columns

    As shown in the attached screen shot, we need to truncate the extra zeros in the xls file (also attached) while leaving 3 decimal places for all other records. ArcGIS is extremely archaic when it comes to dealing with these extra meaningless zeros unfortunately.
    Much thanks for all the help thus far btw!
    Have you tried the formula I posted ? When i ran it against your example sheet it worked for all situations.

    =if(int(f2)=f2,text(e2,"00#") & "-"& text(f2,"#"),text(e2,"00#") & "-"& text(f2,"#.##"))
    Reply With QuoteRegister To Reply
  11. 05-04-2011, 11:38 AM #11
    sirgeo
    sirgeo is offline
    Registered User
    Join Date
    03-10-2011
    Location
    Austin, texas
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Complex Concatenation of 2 columns

    Quote Originally Posted by nimrod View Post
    Have you tried the formula I posted ? When i ran it against your example sheet it worked for all situations.

    =if(int(f2)=f2,text(e2,"00#") & "-"& text(f2,"#"),text(e2,"00#") & "-"& text(f2,"#.##"))
    Thanks Nimrod, yes we tried it, but it just reproduced the results in the RouteSegxxx column.
    Reply With QuoteRegister To Reply
  12. 05-04-2011, 12:08 PM #12
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Complex Concatenation of 2 columns

    one reason could be:

    Sub snb()
   [M2:M3000] = [if(E2:E3000="","",Text(E2:E3000,"000-") & text(F2:F3000,"#.##"))]
End Sub
    If you want to get rid of the last dot in some cells:

    Sub snb_002()
   [M2:M200] = [if(E2:E200="","",substitute(substitute(Text(E2:E200,"000-") & text(F2:F200,"#.##~"),".~",""),"~",""))]
End Sub
    Last edited by snb; 05-04-2011 at 12:17 PM.

    Reply With QuoteRegister To Reply
  13. 05-04-2011, 03:56 PM #13
    sirgeo
    sirgeo is offline
    Registered User
    Join Date
    03-10-2011
    Location
    Austin, texas
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Complex Concatenation of 2 columns

    Quote Originally Posted by snb View Post
    one reason could be:

    Sub snb()
   [M2:M3000] = [if(E2:E3000="","",Text(E2:E3000,"000-") & text(F2:F3000,"#.##"))]
End Sub
    If you want to get rid of the last dot in some cells:

    Sub snb_002()
   [M2:M200] = [if(E2:E200="","",substitute(substitute(Text(E2:E200,"000-") & text(F2:F200,"#.##~"),".~",""),"~",""))]
End Sub
    Thanks snb, that did do what we were looking for... any chance the ~ symbol could be implemented in the formula bar?

    We have actually found a different solution using textpad.
    Source of the problem was the xls file given to us has been modified with so many different formats that the ArcGIS was not able to read it properly.

    By "purging" the xls file we got it to work :
    1) Deleting all non-pertinent columns.
    2) Copy pertinent columns into textpad.
    3) Find/replace all tabs with commas.
    4) Save as a txt file.
    5) Open a fresh Excel session, open the txt file as delimited by comma.
    6) Save as a new xls file.
    7) Run the Join function process in ArcGIS as normally done.
    8) Success!

    Thank you for all your contributions, we learned a few new Excel tricks along the way which are always good to know.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1