Parse string based on Capitalize

[smiling_cheetah]

Hi everyone, 

I come across the following task and would appreciate any help

So there is a single field for name and surname, and value look like that:

Load * inline [
NameSurname
JohnDow
JaneDow
MarySue
];

Is there any approach I can use to separate the values based on the capital letter? (both surname and name start with a capital)

Thank you in advance

[sunny_talwar · Accepted solution]

Try this

MappingLoad:
Mapping
LOAD Chr(64 + IterNo()) as MapFrom,
	 ' ' & Chr(64 + IterNo()) as MapTo
AutoGenerate 1
While (64 + IterNo()) <= 90;

LOAD NameSurname,
	 Trim(MapSubString('MappingLoad', NameSurname)) as Formated;
LOAD * INLINE [
    NameSurname
    JohnDow
    JaneDow
    MarySue
];

[dplr-rn]

If i udnerstand you correctly upper case is the indicator

try this

FindOneOf('MariesSue','ABCDEFGHIJKLMONOPQRSTUVWXYZ',2)

this would return the position of the 2nd capital letter

So

first name would be

Left('MariesSue',
FindOneOf('MariedSue','ABCDEFGHIJKLMONOPQRSTUVWXYZ',2)-1
)

last name -

right('MariesSue',
Len('MariesSue')-FindOneOf('MariesSue','ABCDEFGHIJKLMONOPQRSTUVWXYZ',2)+1
)

[smiling_cheetah]

Wow, that's actually brilliant! 

You iterate through all capital letters to create a mapping with a space. 

The one thing I didn't mention is that those names are not in English, but in Ukrainian, so look like 

Load * inline [
ИмяФамилия
ДжонДоу
ДжейнДоу
МериСью
];

Is there an option to address Ukrainian letters through chr() ? 

And thanks!

[sunny_talwar]

Check for yourself... I just checked =Ord('И') and it gives me 1048 and =Ord('Д') gives me 1044... you just need to know the range for your letters and use that.

[smiling_cheetah]

Hi, thanks, this looks great and is language-independent 

I guess this approach will work for me, really appreciate help

[smiling_cheetah]

Oh, nice, thanks!
Didn't knew of this function "ord()"

[sunny_talwar]

Ya, that one basically is the opposite of Chr()... tells you which number to use with Chr() to find the character 🙂