Trim function in macro
everyone. I saw a good, basic into to macros on the Microsoft site, and one
of the macros I found particularly useful was the one to concatanate two columns into one. I got it to work nicely, until on some data, the new concatanated column would have multiple spaces, probably because the data came from a database field with fixed length. Anyhow, I tried to introduce the Trim function in the macro, but it does not work. Can anyone help me to eliminate multiple white spaces between the two values to be concatanated? Below is the macro as I am trying to use it, and it works fine if you take out the trims. Sub Concatanate() x = 2 Do While Cells(x, 1).Value < "" Cells(x, 3).Value = Trim(Cells(x, 1)).Value + " " + Trim(Cells(x, 2)).Value x = x + 1 Loop End Sub |
Trim function in macro
Got it to work. Changed...
Trim(Cells(x, 1)).Value to... Trim(Cells(x,1).Value) "Greg Snidow" wrote: everyone. I saw a good, basic into to macros on the Microsoft site, and one of the macros I found particularly useful was the one to concatanate two columns into one. I got it to work nicely, until on some data, the new concatanated column would have multiple spaces, probably because the data came from a database field with fixed length. Anyhow, I tried to introduce the Trim function in the macro, but it does not work. Can anyone help me to eliminate multiple white spaces between the two values to be concatanated? Below is the macro as I am trying to use it, and it works fine if you take out the trims. Sub Concatanate() x = 2 Do While Cells(x, 1).Value < "" Cells(x, 3).Value = Trim(Cells(x, 1)).Value + " " + Trim(Cells(x, 2)).Value x = x + 1 Loop End Sub |
Trim function in macro
One thing you should be aware of, while the worksheet TRIM function and the
VBA Trim function both remove leading and trailing spaces, the worksheet version also collapses multiple (consecutive) spaces down to a single space whereas the VBA version does nothing with them. Hence, on a worksheet, this... =TRIM(" A B C ") returns "A B C", whereas within VB code this MsgBox Trim(" A B C ") displays "A B C". If you want or need the worksheet version of TRIM in your VB code, you can call it this way... MsgBox Application.WorksheetFunction.Trim("A B C") which will then display "A B C", same as what gets returned on the worksheet. Rick "Greg Snidow" wrote in message ... Got it to work. Changed... Trim(Cells(x, 1)).Value to... Trim(Cells(x,1).Value) "Greg Snidow" wrote: everyone. I saw a good, basic into to macros on the Microsoft site, and one of the macros I found particularly useful was the one to concatanate two columns into one. I got it to work nicely, until on some data, the new concatanated column would have multiple spaces, probably because the data came from a database field with fixed length. Anyhow, I tried to introduce the Trim function in the macro, but it does not work. Can anyone help me to eliminate multiple white spaces between the two values to be concatanated? Below is the macro as I am trying to use it, and it works fine if you take out the trims. Sub Concatanate() x = 2 Do While Cells(x, 1).Value < "" Cells(x, 3).Value = Trim(Cells(x, 1)).Value + " " + Trim(Cells(x, 2)).Value x = x + 1 Loop End Sub |
All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com