Split text from cell

[jaryszek]

Hi Guys,

I have cell with a lof of values like:

Screenshot_19.png

I want to take pairs and get 2 columns result for each of them.

Screenshot_20.png

Please also find sample workbook.

Please help,

Best,
Jacek

[jindon]

Adjust the output.

Please Login or Register  to view this content.

[jaryszek]

Wow awesome,

thank you!
Jindon i can not decode how this pattern is working.

* - matching preceding element
[] - any signs between brackets
\u = what is it ?
00 = ??
A = ??
0 = ??

Please help,
Best,
Jacek

[jindon]

Actually pattern is simply

Please Login or Register  to view this content.

Your space is "Non-break-space" which is ChrW(160) and the unicode is U+00A0.
In regexp, you can have unicode after \u.
So it means space(s) or non space with "non-breake-space" followed by space(s) or none space.
The code replaces such group to Chr(2).

Understand?

[PCI]

Perhaps next code more at the human level
If you want to loop explain the data lay out

Please Login or Register  to view this content.

[Sintek]

@ PCI

Perhaps next code more at the human level

Yeah, I agree ... jindon is not "OF THIS WORLD" one can only aspire ...

[jaryszek]

Hi Guys,

sorry for my late answer.

Jindon is not of this world but i love his macros and learning from him. What he learned me (like array lists and dictionary within dictionary) i am using now

PCI thank you, what is chr 160? Non breaking space ? it is like a lot of spaces?

I = 1: J = 1

what does it mean?

Jindon

Understand?

not exactly. So this code: U+00A0 is non breaking space here? (chr160)?

( *

what each element mean here?

+

what does it mean?

So it means space(s) or non space with "non-breake-space" followed by space(s) or none space.

Hmm which element is for what?

Thank you!
Best,
Jacek

[jindon]

" *\u00A0 *" = equivalent to " *" & ChrW(160) & " *" as \u00A0 = ChrW(160) which is non breaking space.
When character(s) are are within a brackets, it is a character group.
( *\u00A0 *)+ and the last + means 1 or more consecutive preceding character/character set.
So that it matches to the big space within you string.

You can see the result in msgbox when it replaces like this.
As chr(2) is not printable, so used "@" instead.

Please Login or Register  to view this content.

[jaryszek]

thank you Jindon very much!

it is clear now.

Best,
Jacek

[PCI]

what is chr 160

This the character separator used between text: 160 is the ASCII code

I = 1: J = 1

I equal 1 and J equal 1, the ":" permits to "stick" statement on the same line

[jaryszek]

a nice !

thank you PCI

[PCI]

You are welcome, we are here to learn and to share

[jindon]

thank you Jindon very much!

it is clear now.

Best,
Jacek

Glad you understand and thanks for the rep.
Yes, we share the idea, but not to steal like someone does...

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

[jaryszek]

Hi Guys,

i have new data. What pattern should i have using object:

Please Login or Register  to view this content.

to split the text?

In attachment sample workbook.
Thank you,
Best,
Jacek

Screenshot_29.png

[jindon]

No idea..............

[jaryszek]

jindon,

why ?

Best,
Jacek

[jindon]

How do you convert data in Sheet2 to format in result sheet for yourself?

[jaryszek]

aaa no no,

i only want to split cell A1 like you did in first case.

So result should be:
DAL09 - Dallas first row
Dal10 - Dallas second row etc.

Best,
Jacek

[jindon]

The no need of Regular Expressions.

Please Login or Register  to view this content.

[jaryszek]

thank you very much jindon.

why did you use trim with $ ?

Best,
Jacek

[jindon]

While you are in vbe, got to [view] - [Object Browser]
Enter trim$ in serach dropdown.

You will see functions that has $ and without $.

If you click on Trim, it says Function Trim(String)
while Trim$, Function Trim$(String As String) As String

Means function with $ returns string type explicitly, and said it would be a bit faster.

[jaryszek]

thank you !

Nice explanation !

Best,
Jacek