Truncating cells

[Guest]

I have a column of cells formatted as text containing a number and text i.e:
123.123 text text text
I need some way of truncating the cell to only show the number for
calculation purposes. Any Ideas?

 

[JE McGimpsey]

One way:

Choose Data/Text to Columns... Choose Delimited with a space. in the
Third pane of the Text to Columns dialog, select the columns after the
first and choose "Do not import column (skip)".

(If all numbers are the same length, choose Fixed instead, and click
between the number and the first text, then proceed as above).

 

[Guest]

Let's assume your text is in A1 and let's also assume that the number is
always followed by a space.

=VALUE(LEFT(A1,SEARCH(" ",A1)-1))

 

[Guest]

For numbers beginning, middle or end regardless of spaces:-

Option Explicit
Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
.Pattern = "d+|\d+.\d+"
End With
Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit
For Each C In Myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
C.Offset(0, 1) = Outstring
Next

Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing

End Sub


Mike

 

[Guest]

=REPLACE(A1,FIND(" ",A1),255,"")+0