Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numbers truncating | Excel Discussion (Misc queries) | |||
truncating series | Charts and Charting in Excel | |||
truncating text | Excel Discussion (Misc queries) | |||
Truncating a columns worth of cells not just a single cell. | Excel Worksheet Functions | |||
Reduce # of pages & help truncating cells | Excel Discussion (Misc queries) |