When trying to concatenate the data of 3 cells into one I can't seem to find a way to include spaces to a "number" field. This applies to many records in a database.
Example
Col A = P................ field length 1
Col B = 1234.......... field length 10
Col C = Text........... field length 15
Wanting information to appear in Col D as "P1234......Text..........."
( dots are spaces )
Currently using the concat formula it displays it as "P1234Text"
Can anyone help me please.
Bernz
Last edited by BernzG; 05-19-2005 at 06:08 AM.
Hello BernzG,
In VBA use...
Range("D1").Value = Range("A1") & Range("B1") & Space(10 - Len(Range("B1"))) & Range("C1") & Space(15 - Len(Range("C1")))
In Excel use...
D1 = Concatenate(A1, B1 & Rept(" ", 10 - Len(B1)), C1 & Rept(" ", 15 - Len(C1)))
Sincerely,
Leith Ross
Hi Leith,
Thanks for this - worked perfectly.
Cheers and have a great day.
Bernz
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks