Separate value from name in a cell 2

[mrteacher]

In cell A1 is:
12.53LNAME

I need to separate the 12.53 from LNAME.

I tried
=IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".",A1)-2),"None&quot

All I am getting is the word "None" which is the false part of IF statement. Can someone lend a hand?

Thank in advance.

 

[bluedragon2]

=LEFT(A1,FIND(".",A1)+2)

 

[dan1967]

Is the number format always the same (00.00) before the name piece? Or rather, are there always 2 decimal places?

Dan.

 

[bluedragon2]

=IF(ISNUMBER(VALUE(LEFT(A1,1))),LEFT(A1,FIND(".",A1)+2),"None&quot

If what you are looking for always starts with a number.

 

[SkipVought]

Hi,

Paste this function into a module and use it like any other wprksheet function...

Function GetNumber(sValue As String) As Single
    sNum = ""
    For i = 1 To Len(sValue)
        sbyte = Mid(sValue, i, 1)
        Select Case sbyte
        Case "0" To "9", ".", "-"
            sNum = sNum & sbyte
        Case "A" To "Z", "a" To "z"
            Exit For
        End Select
    Next
    GetNumber = sNum
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

Skip, you are a VBA writing guru

 

[SkipVought]

WTH! It's Friday an' I have nuthin' else to do.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

Tis Friday, and its time for me to start the weekend.

Enjoy

Blue

 

[mrteacher]

Thank you...

I will give these a try...

Yes the cell always starts w/ a number or decimal point first, never an alpha.

Steve

 

[dan1967]

Here is another way to code:

Sub findText()
Dim I, J, K, L
I = Len(Range("a1"))
For K = 1 To I
    If Mid(Range("a1"), K, 1) Like "[a-z]" Then
        GoTo ExitFor
    ElseIf Mid(Range("a1"), K, 1) Like "[A-Z]" Then
        GoTo ExitFor
    End If
Next K
ExitFor:
Range("B1").Value = Left(Range("A1"), K - 1)
Range("C1").Value = Right(Range("A1"), I - K + 1)
End Sub

Dan.

 

[mrteacher]

What I am trying to get out is the NAME part of the cell.

Ideas?

 

[mrteacher]

Dan,

I like the code you are using -- it separates out just what I am after. How do I change this for data going down column A?

Steve

 

[dan1967]

My solution populates cell C1 with the name portion of your cell. This can be modified to suit your needs, or to include a range of multiple cells. The function by Skip could be used in the right() worksheet function:

=RIGHT("12.53LName",LEN("12.53LName")-GetNumber)

Dan.

 

[dan1967]

We must have posted our last comments at the same time. Ooops.

You will need to cycle through the cells and perform this function on each. Include the following at the top:

Set c = Range("A1")
Do While Not IsEmpty(c)
     Set c = c.OffSet(1,0)
Loop
J = c.OffSet(-1,0).Row

Then replace "..." with the For-Next loop, down to, but not including, End Sub, from above in the following:

For L = n to J
...
Next L

The first piece of code above cycles through the cells in a particular column starting with the value you set c as. If you are using column headings in row 1, you can change the reference to start in row 2. The offset function moves the reference point by OffSet(rows, columns). I use the variable J to hold the value of the last row. You should replace n in the For L... line with the row number you are starting with. Also change my cell reference Range("A1&quot with Range("A" & L), assuming your initial value is column A.

Dan.

 

[PepeLeMokko]

Following formula will also extract the number if there are always two decimal places:=LEFT(A1;SEARCH(".";A1;1)+2)