Extract text and numbers

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 ?

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


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)

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.

1 Like

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.

1 Like

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

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

1 Like

@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
3 Likes

Hehe, genious :slight_smile:

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

1 Like

Long, but effective. Thanks

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?

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

Sure :slight_smile: - 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 already gave you a nice solution on the other thread :slight_smile:

1 Like