A Formula that will do Text to Columns

[CarolB]

I have a series of numbers in a cell. I need to be able to pull one section out of it as part of a formula because the people using the Excel spreadsheet do not know Excel well enough to be expected to do Text to Columns for themselves.

For example, 1.1234.12345.1234 and I would need to be able to pull out the 12345 to be able to vlookup to a table with descriptions.

Does anyone know if this is possible?

 

[dallton]

I created this to allow me to convert several columns at once. You may need to make some changesw to get exactly what you need.

Public Sub MultColTxtToCol()
'This routine runs the Text To Column function on all selected columns
Dim intCol As Integer, intx As Integer, intCurrentCol As Integer
intCurrentCol = ActiveCell.Column
intCol = Selection.Columns.Count

For intx = intCurrentCol To intCurrentCol + intCol - 1
Columns(intx).Select
Selection.TextToColumns Destination:=Selection, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Next
MyEndMessage
End Sub

 

[XLPadawan]

I have a series of numbers in a cell. I need to be able to pull one section out of it as part of a formula because the people using the Excel spreadsheet do not know Excel well enough to be expected to do Text to Columns for themselves.

For example, 1.1234.12345.1234 and I would need to be able to pull out the 12345 to be able to vlookup to a table with descriptions.

Does anyone know if this is possible?

How would we know which section to pull out for the VLOOKUP?
1.1234.12345.1234 has four sections. Will there always be four sections? If not, what is the maximum number of sections? Will it always be the same section, e.g. will the third section always be the one used for the VLOOKUP?

 

[Luck]

Try
=MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),2))-1)