Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping a cell value constant trhoughout a list of values | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
#N/A Values : Returned by Formulas vs Entered Manually | Charts and Charting in Excel | |||
Leading Zeros in Numeric Values | Excel Worksheet Functions |