Text separation

[TalkNet_TechnoS]

I need to separate a string of scanned text, in several columns.
Specifically, the barcode of the identity document.
When scanning it, it is presented in the following format:

Procedure N ° @ Surname @ Names @ Sex @ DNI @ Exemplary @ Date Nac @ Date Emission

and I want to separate it in the corresponding fields. Find() only finds me the first match. Is there an expression that fulfills this function?
Thank you very much already

[LeventK]

@TalkNet_TechnoStore
You can try this expression:

SPLIT([_THISROW].[ColumnName],"@")

[TalkNet_TechnoS]

That expression results in “null”, but gave me the idea to do the following:

Index (List (SUBSTITUTE ([DNI], “@”, “,”)), 1)
Index (List (SUBSTITUTE ([DNI], “@”, “,”)), 2) …

That is, I replace the @ with the comma and create a list of values, and in each field I point to the index in the list.

The expression assistant takes the expression as valid, but it does not work. Even the field disappears in the form.

Some help?

[LeventK]

@TalkNet_TechnoStore
You don’t need to substitute the “@” char with comma and use the LIST expression either. SPLIT expression will already convert it into a list and you can read the values with INDEX.

INDEX(SPLIT([_THISROW].[ColumnName],"@"),1)

should work. I have tested and it works as expected:

[TalkNet_TechnoS]

This is the scanned text
00355196384@POU@ALFREDO FABIAN@M@17825572@A@25/08/1967@30/03/2015@200

and this is the expression that I put in App Formula
INDEX(SPLIT([_THISROW].[DNI],"@"),4)

The result is null and it does not show me the form fields, what can I be doing wrong? The type of field is Text

[LeventK]

@TalkNet_TechnoStore
Can you try with setting your column type as LongText and try with removing the [_THISROW]. and use only [DNI]

[Alfredo_Pou]

It worked! Taking out [_THISROW]
Thank you

[Alfredo_Pou]

I have another problem
If I point to the date of birth:
INDEX (SPLIT ([DNI], “@”), 😎
It correctly returns the date in text mode. If I want to calculate the age I do:
HOUR (TODAY () - DATE (INDEX (SPLIT ([DNI], “@”), 8)) / (365.24 * 24))
The problem is that DATE (INDEX (SPLIT ([DNI], “@”), 8))
It returns empty.
I understand that DATE () converts text to date format.
What am I doing wrong?

[Steve]

Alfredo_Pou:

I understand that DATE () converts text to date format.

The textual date must be in a form AppSheet recognizes. A date like 25/08/1967 (DD/MM/YYYY) likely needs to be expressed as 08/25/1967 (MM/DD/YYYY).

[Alfredo_Pou]

It is true!
Is there any way to convert it or should I cut the texts (LEFT, RIGHT, MID) or INDEX() and sort them?

[Steve]

I’m not aware of a way to convert automatically.

[Alfredo_Pou]

It works now!
This is how the expression used to obtain the date of birth in date format:

Date (INDEX (SPLIT (IF (COUNT (SPLIT ([ScanDNI], “@”)) <10,
INDEX (SPLIT ([ScanDNI], “@”), 7),
INDEX (SPLIT ([ScanDNI], “@”), 8)), “/”), 2) & “/” & INDEX (SPLIT (IF (COUNT (SPLIT ([ScanDNI], “@”)) <10,
INDEX (SPLIT ([ScanDNI], “@”), 7),
INDEX (SPLIT ([ScanDNI], “@”), 8)), “/”), 1) & “/” & INDEX (SPLIT (IF (COUNT (SPLIT ([ScanDNI], “@”)) <10,
INDEX (SPLIT ([ScanDNI], “@”), 7),
INDEX (SPLIT ([ScanDNI], “@”), 8)), “/”), 3))

[Steve]

BTW, performing a SPLIT() repeatedly on the same data to extract individual fields is incredibly inefficient. In your particular use case, the inefficiency likely won’t affect you at all, but it’s generally a good idea to make efficient design a habit and pursue it whether strictly needed it not.

It would be more efficient to perform the SPLIT() once, capture the results in a column, and use INDEX() against that column where you need the individual values.

[TalkNet_TechnoS]

@Steve
Thank you very much for the advice, I did it according to your instructions.
Now I have a new string, and the delimiter is the line feed, instead of the @.
What would ASCII be?
I tried Alt 10 and Alt 13, but it does not work.
The string is the following:

DNI

95141384
M
NESTOR RODRIGO
ABAN VARGAS
19-10-1990
BOLIVIA
BLANCO ENCALADA
2570

1

VILLA CELINA
1772
6-4-2016
4-2-2020
A.2.1,B.1
A -
552735428

[Steve]

Try:

SPLIT([DNI], "
")

Note the separator is a newline, typed as "-Enter-".

[TalkNet_TechnoS]

It does not work. Any other suggestions?

[Steve]

Hmm… It works fine for me. Please cut and paste the exact expression you used.

[TalkNet_TechnoS]

This is the string:

DNI
95141384
M
NESTOR RODRIGO
ABAN VARGAS
19-10-1990
BOLIVIA
BLANCO ENCALADA
2570

1

VILLA CELINA
1772
6-4-2016
4-2-2020
A.2.1,B.1
A -
552735428

[Steve]

The expression, the formula.

[TalkNet_TechnoS]

Sorry

INDEX(SPLIT([ScanDNI],"
"),4)

[Steve]

NESTOR RODRIGO is what that expression gives me.

[Steve]

I’d be happy to look at you app if you’re interested, though it probably wouldn’t be until tomorrow. To give me access, from the app editor, go to Users in the left-side navigation, select the Users tab, enter my email (sc2758g@gmail.com), set Add as co-authors? to ON, and click Add user + send invite. You will remove my access from the same location.

[TalkNet_TechnoS]

@Steve I copied the expressions in another app, and when I set the ScanDNI column as Long Text, it does not allow me to activate the “Scannable” option and it shows the following error:

[Steve]

I don’t know why that would happen. @Aleksi, do you?

[TalkNet_TechnoS]

Thank you very much @Steve, but I managed to make it work.
ScanDNI was as a Text type and that caused the line break to be lost, I configured it as Long Text and it worked perfectly.

[Tin_Nguyen1]

I also have a split question.
I have a prescription
[refraction] = -1.00 -0.75 x 155
I want to split into 3 fields and store in 3 different rows
row 1 = -1.00
row 2 = -0.75
row 3 = 155

I tried INDEX(SPLIT([refraction],"
"),2)

It didn’t work. It didn’t split it.

[Bahbus]

help.appsheet.com

EXTRACTNUMBERS()

Extract list of numeric values within text value

[Marc_Dillon]

Tin_Nguyen1:

I tried INDEX(SPLIT([refraction],"
"),2)

It didn’t work. It didn’t split it.

That’s because your text is not delimited by a newline, it is delimited by spaces, and an ‘x’. You should try @Bahbus’s expression. But this should work as well:

val1= INDEX( SPLIT( [refraction] , " " ) , 1 )
val2= INDEX( SPLIT( [refraction] , " " ) , 2 )
val3= INDEX( SPLIT( [refraction] , " " ) , 4 )

Take notice that the 3rd value is the 4th index, because the ‘x’ character is the 3rd when splitting by a space. This of course assumes that your [refraction] text will always come in like:

“val1 val2 x val3”

[Tin_Nguyen1]

Thank you. It worked. However, in my refraction, I will sometimes have a + instead of -.
For example,
+5.00 -2.00 x 180 converts to +5.50 -1.75 x 180
I need to split them to do a calculation and put them back together. Is there any way I can keep the + during the calculation? Right now, I split them up. I calculate them and then concatenate them back together. However, the only way I can put back the +5.50 is by converting it into a text and adding a “+” in front of it.

[Marc_Dillon]

Tin_Nguyen1:

Is there any way I can keep the + during the calculation?

No. And you might need to get rid of it to do any math calculations, use NUMBER() or DECIMAL() to convert text into numbers.

Tin_Nguyen1:

Right now, I split them up. I calculate them and then concatenate them back together. However, the only way I can put back the +5.50 is by converting it into a text and adding a “+” in front of it.

Sounds like you’ve got it.