Text separation

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

0 29 2,725
29 REPLIES 29

LeventK
Participant V

@TalkNet_TechnoStore
You can try this expression:

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

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?

@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:

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

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

It worked! Taking out [_THISROW]
Thank you

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?

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).

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
Participant V

Iโ€™m not aware of a way to convert automatically.

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
Participant V

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.

@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

Try:

SPLIT([DNI], "
")

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

It does not work. Any other suggestions?

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

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

The expression, the formula.

Sorry

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

NESTOR RODRIGO is what that expression gives me.

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.

@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:

I donโ€™t know why that would happen. @Aleksi, do you?

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
Participant III

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.

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
Participant III

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.

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

Sounds like youโ€™ve got it.

Top Labels in this Space