A Formula that will do Text to Columns


Joined
Apr 17, 2012
Messages
3
Reaction score
0
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?
 
Joined
Dec 31, 2012
Messages
3
Reaction score
0
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
 
Ad

Advertisements

Joined
Jun 9, 2016
Messages
32
Reaction score
5
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?
 
Last edited:
Ad

Advertisements

Joined
Dec 28, 2017
Messages
8
Reaction score
1
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top