Truncating cells
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? |
Truncating cells
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). In article , Simon wrote: 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? |
Truncating cells
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)) -- HTH, Barb Reinhardt "Simon" wrote: 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? |
Truncating cells
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 "Simon" wrote: 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? |
Truncating cells
=REPLACE(A1,FIND(" ",A1),255,"")+0
"Simon" wrote: 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? |
All times are GMT +1. The time now is 07:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com