Numbers - Trim Text to Right at first space

Question

marsofearth Author

Level 1

21 points

Numbers - Trim Text to Right at first space

We receive information from an online form, and I would like to know an Apple Numbers formula to trim.

Currently using google sheets to do the trim see the formula at right in cell C1 (that lives in cell B1).

Select Product

Product

(see formula to Right)

FORMULA="Arrayformula(IF(A1:A = "Select Product","Product",if(istext(A1:A),left(A1:A, find(" ", A1:A) -1),)))"

This-Is-Product-SKU1 -- This is Product 1 with some information for users

This-Is-Product-SKU1

This-Is-Another-Product-SKU --- Some information for users and other text

This-Is-Another-Product-SKU

It's my understanding that numbers can not do arrayformula's and that is fine, I'll just copy down.

Thanks for the help and guidance,

Mars

Mac Pro, OS X Yosemite (10.10.2), Early 2013 4 Core

Posted on Jan 19, 2017 7:19 AM

Reply

Answer 1

Yellowbox

Level 7

22,105 points

Jan 19, 2017 8:30 AM in response to marsofearth

Hi Mars,

See if this works for you:

Formula in B2 (and Fill Down)

=FIND(" ",A2)

Formula in C2 (and Fill Down)

=LEFT(A2,B2)

Don't worry about the red error triangles. They occur because there is nothing in an A cell. We can get rid of them.

Happy Numbering!

Ian.

Reply

Answer 2

Yellowbox

Level 7

22,105 points

Jan 19, 2017 11:07 AM in response to marsofearth

Hi Mars,

As per your formulas I nested them for the final working solution:

That makes you a Numbers Guru. I have always found it difficult to combine simple steps into a complex formula.

When is your "Working With Numbers" book coming out?

Never. Solving problems in Numbers for Mac is one of my hobbies. I work just one step at a time.

For your next trick, try this formula to remove the trailing space (that I forgot about 😊)

Note the "-1" in the new formula in C.

Compare Column B and Column D.

Formula in D2 =LEN(C2)

Now you are a Numbers expert. Keep posting. We need you.

Regards,

Ian.

Reply

Answer 3

marsofearth Author

Level 1

21 points

Jan 19, 2017 8:30 AM in response to Yellowbox

Thank you Ian!

Really appreciate how you step formulas out, makes understanding the process very easy.

As per your formulas I nested them for the final working solution:

Select Product Output	SKU Extraction	formula preview in column B
ProductSku1 -- description of the product sku 1	ProductSku1	LEFT(A2,(FIND(" “,A2)))
ProductSkuTwo ----- another description	ProductSkuTwo	LEFT(A3,(FIND(" ",A3)))
thisIsAnotherSKU ---- descriptions just keep coming…	thisIsAnotherSKU	LEFT(A4,(FIND(" ",A4)))

PS. When is your "Working With Numbers" book coming out? 🙂

Mars

Reply

Answer 4

marsofearth Author

Level 1

21 points

Jan 19, 2017 2:45 PM in response to Yellowbox

Thanks Ian,

This is good to know, looks like the formula on Google Sheets and Numbers is a direct translation with exception of the Array formula.

I should test out more Google Sheet formulas in Numbers and see which pieces break.

Cheers!

mars

Reply