LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #2   Report Post  
Senior Member
 
Location: Hyderabad
Posts: 237
Thumbs up

Quote:
Originally Posted by mgbcab View Post
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.
Attached Files
File Type: zip macro.zip (12.8 KB, 134 views)
__________________
Thanks
Bala
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I center a number in "comma" format? Eric Excel Discussion (Misc queries) 8 April 2nd 23 07:10 PM
excel 2003: how do I fix: "Style 'Comma' not found" jrp9206 Excel Worksheet Functions 4 March 25th 16 07:09 AM
syntax for "IF" commend to check for multiple empty cells bf comma Chris Excel Worksheet Functions 4 September 3rd 07 12:02 PM
Interpreting "comma" where an optional argument is Epinn Excel Worksheet Functions 4 January 17th 07 12:04 AM
no comma separator for ="total"&"$ "&sum(a1:a10) Jeff Excel Worksheet Functions 3 August 9th 06 05:06 PM


All times are GMT +1. The time now is 10:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"