Im pretty sure there is no direct formula to do this, (and a user defined formula is limited to changing only the cell containing its call). And here is the reason; the VBA recorded to do a text to columns (for that is what it is called) is as follows.
Selection.TextToColumns Destination:=Range("B3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
So back to macro. They're not so bad! You could run down the fitered column checking if the cell concerned is visble and if so, doing the necessary similar to the above).
Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True
' do the text to columns bit
Wrap the whole in a suitable loop.
Bookmarks