Trim Special Chars & Small Caps and Return the Big Caps and Space only

[Dahlia]

Hello Excel Experts,

^[Ss][Oo][Cc][Ii][Aa][Ll] + ?[Rr][Oo][Aa][Mm] [0-9]+ ?$

I need to return the above as SOCIAL ROAM.

Anyone can help, pls?

Thousands in a list to do. Thought of formula would be fast solution. If can get the right one.

Appreciate your help.

Thanks in advance.
DZ

[Glenn Kennedy]

tRY THIS udf:

=TRIM(ExtractCap(A1))

Code, pasted into a module:

Please Login or Register  to view this content.

Enable macros on opening...

[Dahlia]

Wowww!! This is really awesome!!! So fast you did it!! Tqvm!!

Now I have another challenge when there are numbers in between :-

^[Tt][Ee][Xx][Tt][12]00 ?$ ==> TEXT100, TEXT200
^[Tt][Hh][Ee][12]0 ?$ ==> THE10,THE20
^8[Tt][Hh][Aa][Tt]7[18]53 ?$ ==> 8THAT715,8THAT783
^[Yy][Ee][Ss] ?[Ww]1[05]$ ==> YES W10,YES W15
^[Oo][Ll][Aa] 1.2[Gg][Bb] ?$ ==> OLA 1.2GB


Would you be able to modify your udf to filter based on the pattern above as well?

Thanks in advance.

DZ

[Glenn Kennedy]

You're welcome.

I'm not sure about that, as I'm not much good with VBA.

can you post the expected results from these strings?

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[AliGW]

Glenn - the expected results are on the right, I think, and they do rather shift the goalposts!

[Dahlia]

You're welcome.

I'm not sure about that, as I'm not much good with VBA.

can you post the expected results from these strings?

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

You look expert enuff to me..

Owh.. the expected result were all put next to the pattern after symbol ==>

Maybe I just display them in a different way down here for your easier understanding:-

CURRENT:
^[Tt][Ee][Xx][Tt][12]00 ?$
^[Tt][Hh][Ee][12]0 ?$
^8[Tt][Hh][Aa][Tt]7[18]53 ?$
^[Yy][Ee][Ss] ?[Ww]1[05]$
^[Oo][Ll][Aa] 1.2[Gg][Bb] ?$

EXPECTED:-
TEXT100, TEXT200
THE10,THE20
8THAT715,8THAT783
YES W10,YES W15
OLA 1.2GB

The behaviour is something like below:
if the number is outside [], it shud be treated as fixed digit
if 1[35], it should come out as 13, 15
if 1[56][98], it should come out as 159,168
if 30[2][46]7, it should come out as 30247, 30267

hope it helps to clarify...

Thanks in advance

[Glenn Kennedy]

I assumed that they were part of the string!!

[Dahlia]

Glenn - the expected results are on the right, I think, and they do rather shift the goalposts!

Sorry, should I post as a new thread then?

Please advise.

Thanks in advance.

DZ

[Glenn Kennedy]

No that's beyond me. i can extract the text and numbers, but am not sure where to go to meet your requirement.


Maybe someoen else will jump in... Are there regular pattersn to your real data?? That might help.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Dahlia]

I assumed that they were part of the string!!

Do you need me to post it as separate thread, Sir?

I can do that and put this as SOLVED first.

Please advise.

Thanks in advance.
DZ

[Dahlia]

No that's beyond me. i can extract the text and numbers, but am not sure where to go to meet your requirement.


Maybe someoen else will jump in... Are there regular pattersn to your real data?? That might help.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

This helps a lot too anyway. Tqvm, Sir! Really appreciate it.

[Dahlia]

Will put this as SOLVED as per original SUBJECT.

Thank you so much to Mr. Glenn Kennedy. You're awesome!

[AliGW]

No, please don't start a new thread. Other people may be able to help. I've marked the thread unsolved.

[Glenn Kennedy]

You're welcome.

---

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Dahlia]

No, please don't start a new thread. Other people may be able to help. I've marked the thread unsolved.

Alright then. Thanks!

[JohnTopley]

There appears to be an inconsistency here base on my interpretation of the logic:

^8[Tt][Hh][Aa][Tt]7[18]53 ?$ with results of

8THAT715,8THAT783

Here 53 is treated as [53]

To me, this does not accord wih ..

if the number is outside [], it should be treated as fixed digit

so I would have expected result to be

8THAT7153,8THAT7853

Please add a file with a good set of sample data.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Dahlia]

I have a correction to make :-

if 1[56][98], it should come out as 159,158,169,168

[Dahlia]

You are correct. I'll work out on the sample data and revert to you soon. Tq

[Dahlia]

Here is my sample data for your kind perusal, Sir.

[Dahlia]

Hi Excel Experts,

I guess it must be very challenging to filter those with numbers within/without the brackets. But may I suggest if someone can just filter out everything except for
BIG CAPS
Space
Numbers without the brackets
Numbers within the brackets (means don't trim the brackets when there's number in it, be it in range or single numbers, eg. [27],[0-9],1[5]90)

Sample:
^8[Tt][Hh][Aa][Tt]7[18]53 ?$ ==> 8THAT7[18]53 with this kinda output I can explain to customers already.


Hopefully the above suggestion would give someone an insight.

Thank you in advance.
DZ

[Dahlia]

No that's beyond me. i can extract the text and numbers, but am not sure where to go to meet your requirement.


Maybe someoen else will jump in... Are there regular pattersn to your real data?? That might help.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

This one would help if whichever the brackets that have numbers or range of numbers not being stripped off.. also it would means, if there's a range of numbers, eg. 0-9, the "-" symbol should not be trimmed off too..

Thank you in advance.
DZ

[JohnTopley]

A partial solution as your post #20

Please Login or Register  to view this content.

in column B

=Extract_text(A2)

[Dahlia]

Wowww!!! This is really awesome, Sir!!! Thanks a million!!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

By the way, please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[JohnTopley]

A minor change will insert the blank:

comment out the line below (add ' at beginning of line)

' text_string = Replace(text_string, " ", "")

Does this solution satisfy your requirement?

[Dahlia]

A minor change will insert the blank:

comment out the line below (add ' at beginning of line)

' text_string = Replace(text_string, " ", "")

Does this solution satisfy your requirement?

Yes, Sir. Indeed I already did that after looking at your sample file. Thanks again for your big help!

I already set to SOLVED for this thread. Thanks again.