Truncate a series of numbers

[Carrie_Loos via OfficeKB.com]

Hi -

I am trying to truncate these numbers to only 2 sections; example: from 51230.
14.6.24 to 51230.14 There can be multiple quantities of numbers in each
section; it can be 51230.1434.66.789. I used the function below but it leaves
a "." at the end which I do not want. As in the example I need to have just
51230.14 Any suggestions?

Thanks

=LEFT(K2,FIND(CHAR(7),SUBSTITUTE(K2,".",CHAR(7),2)))

Thanks in advance,
Carrie

 

[Greg Maxey]

Probably very crude, but best I can think of right now:

Sub GetRevisedString()
Dim oStr As String
oStr = Selection.Range.Text
MsgBox ReviseString(oStr)
End Sub

Function ReviseString(strIn As String)
Dim oFirstChr As String
Dim i As Long
Dim j As Long
Dim tempStr As String
strIn = Trim(strIn)
oFirstChr = Left(strIn, 1)
For i = 1 To Len(strIn)
If Mid(strIn, i, 1) = "." And j = 1 Then
ReviseString = tempStr
Exit Function
ElseIf Mid(strIn, i, 1) = "." Then
tempStr = tempStr & Mid$(strIn, i, 1)
j = 1
Else
tempStr = tempStr + Mid$(strIn, i, 1)
End If
Next i
ReviseString = tempStr
End Function

Hi -

I am trying to truncate these numbers to only 2 sections; example:
from 51230.
14.6.24 to 51230.14 There can be multiple quantities of numbers in
each section; it can be 51230.1434.66.789. I used the function below
but it leaves a "." at the end which I do not want. As in the example
I need to have just 51230.14 Any suggestions?

Thanks

=LEFT(K2,FIND(CHAR(7),SUBSTITUTE(K2,".",CHAR(7),2)))

Thanks in advance,
Carrie

--
--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the
strong man stumbles, or where the doer of deeds could have done them
better. The credit belongs to the man in the arena, whose face is
marred by dust and sweat and blood, who strives valiantly...who knows
the great enthusiasms, the great devotions, who spends himself in a
worthy cause, who at the best knows in the end the triumph of high
achievement, and who at the worst, if he fails, at least fails while
daring greatly, so that his place shall never be with those cold and
timid souls who have never known neither victory nor defeat." - TR

 

[Graham Mayor]

How about

Dim vNum As Variant
With Selection
.HomeKey wdStory
Do While .Find.Execute("[0-9.]{5,}", MatchWildcards:=True)
Selection.Range.Select
vNum = Split(Selection.Range.Text, ".")
Selection.TypeText (vNum(0) & "." & vNum(1))
Loop
End With


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Tony Jollans]

I suggest you ask in an Excel newsgroup, as your problem is with an Excel
formula. This group is for Word.

 

[Carrie_Loos via OfficeKB.com]

Thanks Tony - I didn't realize I was in Word newsgroup

I suggest you ask in an Excel newsgroup, as your problem is with an Excel
formula. This group is for Word.

Hi -

[quoted text clipped - 13 lines]

Thanks in advance,
Carrie