Can I paste in only absolute value of a measure (no units)?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying paste in a large series of values that have a unit of measure
designator attached, therfore, is pasting in a text, rathe than a number.
Example: 120h, where I would like to paste in only 120. Is ther way to
convert text to number, or drop the "h" when pasting?
 
I cannot think of a Paste trick to do this (would be nice).

Is it always a SINGLE letter on the right?
then use a helper column with =--MID(A1,1,LEN(A1)-1); do a copy followed by
paste special -> values. Now the helper column can replace the original
column.
The double negation converts text to number

If there are a limited number of units (say H, KM or DAYS):
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",""),"km",""),"days","")

best wishes
 
Is this what you want?

Sub copypart()
range("e6").Value = Left(range("e5"), 3)
End Sub
 
DMC said:
I am trying paste in a large series of values that have a unit of measure
designator attached, therfore, is pasting in a text, rathe than a number.
Example: 120h, where I would like to paste in only 120. Is ther way to
convert text to number, or drop the "h" when pasting?
Click to expand...

Another possibility:

=MAX(IF(ISNUMBER(VALUE(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))),VALUE(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))))

To be confirmed with <Shift><Ctrl><Enter>, also if edited later.

Works with any number of characters in the designator.
 
Back
Top