Hello Forum Leaders,
I have text in single column such as Text,Text with Numerical,semi colon,back slash and lower and upper case character.
In Column A2 as a long text. The Formula considered the Text two character only.
Another one problem The Formulas couldn’t considered some Texts (XXXXX)and Lower and Upper Case character Problems(XXX-xxx).
File attached for your reference.
Please help me.
The problem/question is unclear.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
JBeaucaire; Thank you so much for considered my querry.
QUERY 1
The following text in column A2.
; LOCATIONS/B/12/C ; LOCATIONS/B/13/C ; LOCATIONS/B/15/C ; LOCATIONS/B/18/B ; LOCATIONS/C/05/B ; LOCATIONS/C/06/C ; LOCATIONS/C/13/C ; LOCATIONS/C/19/C ; LOCATIONS/E/04/C ; LOCATIONS/E/06/C
I have apply the following formula in B2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A37,"LOCATIONS",REPT(" ",99)),99,198)),"M-B",REPT(" ",99)),99)),"WAY",REPT(" ",99)),99)),"Received In",""),"/"," ")),";"," ",2),";"," ")," ","//"))," ","/"),"///"," "),"//","")
value return is first two text character only such as /B/12/C and B/13/C. But my required format is B/12/C B/13/C B/15/C B/18/B C/05/B C/06/C C/13/C C/19/C E/04/C E/06/C
Query 2.
The following text in column A2
; AI/// ; MP4-12C/Tralier/01/ ; LOCATIONS/Received In// ;LOCATIONS/A+Q Placing Way// ;LOCATIONS/Q/07/A ; LOCATIONS/Q/07/B ; LOCATIONS/Q/07/FLOOR
I have apply the following formula in B2. return the value is A+Q/Placing/Way. But My required format is Q/07/A Q/07/B Q/07/FLOOR.
Please help me.
Hello Saxon,
I received your PM.
Wow! This workbook contains many more exceptions and inclusions than the previous post.
I am amazed that my formula actually worked with some of them.
My formula (column B) is derived by mostly brute force. At that time you were looking for a shorter formula than you currently had.
I can not think of a way to do this without more brute force and an enormous formula.
I am not VBA proficient, but my sense is telling me that you might consider such solution and include a complete list of
those exceptions and inclusions.
I am afraid that I am out of ideas.
Sorry.
Dave
Dave
I just given a try with my limited knowledge in Regular Expression.
Unable to post code in the body of this post because of securi blocking.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
I'd use the UDF:
Please Login or Register to view this content.
FlameRetired , Sixthsense and snb
Thank you so much for your help and suggestion.its a magic and Unbelievable.....
I had the same last week, by editing out various lines of the code I was posting, I found that the block was caused by nesting multiple levels of Replace.Originally Posted by :) Sixthsense :)
I got around it by using this method in my code.
Just had another idea, test post using your code to see if it works.Please Login or Register to view this content.
** Code below credited toSixthsense
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks