Quote:
Originally Posted by mgbcab
Ultimately, I want to do a text to columns method but 1st I need to make the
data work properly.
I have close to 9000 items of vary degrees of numbers and text. I want a
formula that will insert a comma after the last " or ' in a cell, reading
from left.
3/4" x 1/2" pvc pipe
1/2" x 1/2" x 6' black pipe
1' insulated filter
This way when i do the text to columns, i can separate the by commas. The
purpose is to separate the sizes and the descriptions.
|
i have attached sample spread sheet with solution, please refer to it,
assuming the input values in column A, try this formula in column B,
=reversestring(CONCATENATE(LEFT(reversestring(A2), (IF(ISERROR(SEARCH("""",reversestring(A2),1)),SEAR CH("'",reversestring(A2),1),SEARCH("""",reversestr ing(A2),1)))-1),",",RIGHT(reversestring(A2),LEN(reversestring(A 2))-(IF(ISERROR(SEARCH("""",reversestring(A2),1)),SEAR CH("'",reversestring(A2),1),SEARCH("""",reversestr ing(A2),1))-1))))
and reversestring is a user defined vba function.
'created and edited by bala sesharao
Option Explicit
Public Function ReverseString(Text As String)
ReverseString = StrReverse(Text)
End Function
all the best.