Concatenate values
 

Trying to concatenate data that is imported in a file but having a problem.

..Offset(0, 2).Value = Right("0000000" & myID & "h", 4)

Data Want Get
2C0 2C0h 2C0h
402 402h 402h
0 000h 0 h - two spaces between 0 and h
14 0014h 14 h - one space b/w 14 and h
2 0002h 2 h - two spaces between 2 and h

Second problem is some of the data that is imported is "00" and I want to
keep this
format but when imported the "00" becomes "0".

Any suggestions?

Thanks

Concatenate values
 

Dan a écrit :
Trying to concatenate data that is imported in a file but having a problem.

..Offset(0, 2).Value = Right("0000000" & myID & "h", 4)

Data Want Get
2C0 2C0h 2C0h
402 402h 402h
0 000h 0 h - two spaces between 0 and h
14 0014h 14 h - one space b/w 14 and h
2 0002h 2 h - two spaces between 2 and h

Second problem is some of the data that is imported is "00" and I want to
keep this
format but when imported the "00" becomes "0".

Any suggestions?

Thanks



Hi Dan,

Looks like your 0 is 0 followed by two spaces. To remove (not tested),
you can try :
..Offset(0, 2).Value = Right("0000000" & trim(myID) & "h", 4)

If ever spaces were not chr(32) but chr(160) [importing...], you'll have
to replace this(these) character(s) by nothing. Could lead to :
myID = application.substitute(myID,chr(160),"")
..Offset(0, 2).Value = Right("0000000" & trim(myID) & "h", 4)


Concerning your "00" import, probably "00" is NOT "00" (text) but 00
(numeric). Excel converts as zero so 0.
During import options, set the columns to text and the problem will
disappear.

Finally you probably realized that above is NOT worksheetfunction.
Please give preference to programming group where you also posted.

HTH
FxM

Concatenate values
 

"FxM" wrote:

Dan a écrit :
Trying to concatenate data that is imported in a file but having a problem.

..Offset(0, 2).Value = Right("0000000" & myID & "h", 4)

Data Want Get
2C0 2C0h 2C0h
402 402h 402h
0 000h 0 h - two spaces between 0 and h
14 0014h 14 h - one space b/w 14 and h
2 0002h 2 h - two spaces between 2 and h

Second problem is some of the data that is imported is "00" and I want to
keep this
format but when imported the "00" becomes "0".

Any suggestions?

Thanks



Hi Dan,

Looks like your 0 is 0 followed by two spaces. To remove (not tested),
you can try :
..Offset(0, 2).Value = Right("0000000" & trim(myID) & "h", 4)

If ever spaces were not chr(32) but chr(160) [importing...], you'll have
to replace this(these) character(s) by nothing. Could lead to :
myID = application.substitute(myID,chr(160),"")
..Offset(0, 2).Value = Right("0000000" & trim(myID) & "h", 4)


Concerning your "00" import, probably "00" is NOT "00" (text) but 00
(numeric). Excel converts as zero so 0.
During import options, set the columns to text and the problem will
disappear.

Finally you probably realized that above is NOT worksheetfunction.
Please give preference to programming group where you also posted.

HTH
FxM

Thanks for the response. Did not see your response before I tried the TRIM
function which corrected the problem.

Will try setting the cell to text to maintain the "00" zero zero value.

Thanks