CODEWITHSUNDEEP
excelconcatenationexcel-2013vba
Chad Portman
Chad Portman

Reputation: 1216

pulling int out of string to do concatenation

I am using excel 2013 and I am trying to combine a variable containing a string to a portion of another variable containing a string. The portion I need however would be an integer and I would like the leading zeros removed.

So for example I have 

LName = Application.WorksheetFunction.VLookup(OCR, Sheets("Whitelist").Range("Whitelist"), 3, False)

This variable will be someone's last name. I also have 

OCR = Right(Left(UserIn, 3), 2) & Left(Right(UserIn, 11), 9)

This will be the person member ID it will look something like this DC000123456. OCR will always be 11 char long and the first two will always be letters with the last nine always being numbers starting with an unknown number of 0's.

I am wanting to make a variable called concat that would be the Last name then space and the numbers from their id with the leading 0's.

I have tried 

Concat = Lname &" "&Right(OCR, 9)
Concat = Lname &" "&Cint(Right(OCR, 9))
Concat = Lname &" "&Cstr(Cint(Right(OCR, 9)))

All of these give errors.

I was wanting to remove the leading zeros. Sorry forgot to include example of end result.

Upvotes: 0

Views: 44

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12645

I didn't get if you want to keep or remove the zeros in front of the ID.

Given this: 

LName = "Matteo"
OCR = "DC000123456"

If you want to keep them:

Concat = LName & " " & Right(OCR, 9)
Debug.Print Concat
>>> Matteo 000123456

If you want to remove them:

Concat = LName & " " & CDbl(Right(OCR, 9))
Debug.Print Concat
>>> Matteo 123456

*note: need to use the function CDbl, your CInt would give an Overflow because the ID number is larger than the allowed size of Integer

Upvotes: 2

Related Questions