Extract text and numbers

[Jeremy_F]

1 - How to extract all text values and ignore number? say Product 154Z, product name 325 SC, product new name S41V etc

2 - Find the position of the first number in a text ?

[MultiTech]

You could make use of SUBSTITUTE() to remove numbers, and FIND() will give you the position of whatever you search for.

help.appsheet.com

SUBSTITUTE()

Text with replacements

help.appsheet.com

FIND()

Position of fragment in text

[Jeremy_F]

Thanks, but that doesn’t allow extracting of text and numbers, e.g, extract all text before the first number (e.g extract the text ‘product’ from product 451SC, “product name F” from product name F3125 etc)

[Lynn]

Could you show some more examples of before and after of what you are trying to do. You may need to do it in a spreadsheet.

[Jeremy_F]

1 - To extract all text values before a number, for example, extract product from product 13, extract product name from product name 753VS, Item second TK from Item second TK425546

2 - Find the first number that appears in a text, Test125KZ will give first number as 1, Part73259 will give first number as 7

N.B. in google sheet, I can use regexextract and regexmatch, but I want to get these values when using the app

[Bellave_Jayaram]

@Jeremy_F you are essentially asking for REGEXTRACT and REGEXMATCH or more powerful string functions which don’t exist as yet - you could add that to the Feature Request.

[MultiTech]

Yeah, there’s no “easy” way to do this. I assume you’re wanting to systematize things, but unfortunately there isn’t a way to do things like this on AppSheet; you have to “brute force” things like this, building an algorithm-like formula to analyze things and give you the output you want.

[Fabian_Weller · Accepted solution]

@Jeremy_F You can try thin one:

LEFT("product name 753VS",
ANY(SORT(LIST(
FIND("0","product name 753VS"),
FIND("1","product name 753VS"),
FIND("2","product name 753VS"),
FIND("3","product name 753VS"),
FIND("4","product name 753VS"),
FIND("5","product name 753VS"),
FIND("6","product name 753VS"),
FIND("7","product name 753VS"),
FIND("8","product name 753VS"),
FIND("9","product name 753VS"))
- LIST("0")))-1)

• LIST(FIND()): Find the position of numbers 0 to 9, and create a LIST of those positions.
• -LIST(“0”): Delete all Zeros from that LIST.
• SORT: Order this LIST from 1to9.
• ANY: Take the first number.
• LEFT(“product name 753VS”, “Position” - 1): Decrease the Position -1

[Laurens_Swart]

Hehe, genious

[Jeremy_F]

Long, but effective. Thanks

[hunaism]

Fabian:

LEFT(“product name 753VS”, ANY(SORT(LIST( FIND(“0”,“product name 753VS”), FIND(“1”,“product name 753VS”), FIND(“2”,“product name 753VS”), FIND(“3”,“product name 753VS”), FIND(“4”,“product name 753VS”), FIND(“5”,“product name 753VS”), FIND(“6”,“product name 753VS”), FIND(“7”,“product name 753VS”), FIND(“8”,“product name 753VS”), FIND(“9”,“product name 753VS”)) - LIST(“0”)))-1)

hmmm nice, is it possible to do the oposite (EXTRACT NUMBERS does not work for me) and extract all digits, even if they appear twice or more?

[Fabian_Weller]

Would you mind to share an example? I’m happy to help.

[hunaism]

Sure - I’m reading with OCR the electronic odometer of my motorcycle

It gives the text like:

M
km/h
ODO 7 1868

The real value I would like to get is 71868
Extruct number is not giving expected value… https://community.appsheet.com/t/extracting-number-am-i-doing-something-wrong/24411

[Steve]

There is no good way to do what you want within AppSheet itself.

[Fabian_Weller]

@Steve already gave you a nice solution on the other thread