Select only a part of the number in a field where thre digits are random

[Slazer2k]

Dear Forum,

I have a problem with an Excel file where MID or LEFT don't work or maybe I am not using it right

in D I have

A48662.401.01.65.112

445460.A48662.603.01.65.200

<tbody>
</tbody>

etc now I need a column lets name it Z where it would show only the part "A48662.***" where the * can change from 001 to 999 i don't need anything from before or behind that's why LEFT or MID don't work for me

any ideas?

 

[Swapnil Shah]

Given a specific case use.

= Mid(D1,find("A48662",D1,1),10)


Thanks
Swapnil Shah

 

[Slazer2k]

Hello Swapnil,

thanks for that it works most of the time but sometimes it shows

401.01.65. rather than A48662.603

<tbody>
</tbody>

when the field was 445460.A48662.603.01.65.200 for example

 

[Rick Rothstein]

I have a problem with an Excel file where MID or LEFT don't work or maybe I am not using it right

in D I have

A48662.401.01.65.112

445460.A48662.603.01.65.200

<tbody>
</tbody>

etc now I need a column lets name it Z where it would show only the part "A48662.***" where the * can change from 001 to 999 i don't need anything from before or behind that's why LEFT or MID don't work for me

Can you use a UDF (user defined function)? If so...

Function GetCode(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, ".")
  For X = 0 To UBound(Parts) - 1
    If Left(Parts(X), 1) Like "[A-Za-z]" Then
      GetCode = Parts(X) & "." & Parts(X + 1)
      Exit For
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetCode just like it was a built-in Excel function. For example,

=GetCode(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Swapnil Shah]

hi,

Are you sure your formula is referring to correct cell? as in given example I am not able to find text "401.01.65." in "445460.A48662.603.01.65.200" ? I did check given formula with provided field name it gives me "A48662.603" as an output

also in any of the case the first 6 character of the found result has to be "A48662" else formula will throw an error , there is nothing other can come.

Thanks
Swapnil Shah

 

[Slazer2k]

you were right thanks a lot Solved now