Functions For Working With Text In Mac Numbers

macmost.com/e-2590 Learn how to test and manipulate text in cells in Mac Numbers. You can search for characters, replace them, grab parts of text and more.
FOLLOW MACMOST FOR NEW TUTORIALS EVERY DAY
▶︎ KZbin - macmost.com/j-youtube (And hit the 🔔)
▶︎ KZbin Email Notifications - macmost.com/ytn ⭐️
▶︎ MacMost Weekly Email Newsletter - macmost.com/newsletter ✉️
SUPPORT MACMOST AT PATREON
▶︎ macmost.com/patreon ❤️
#macmost #mactutorial #numbers

[@ryanmooncat3949]

Imagine if every educator was as clear and competent as Gary. Sheesh. The world would be a smarter place.

[@loisskiathitis8926]

A very useful video tutorial today. I’m so grateful for the visuals. Thank you, Gary! 👏🏻👍❤️

[@businessbeaute6229]

You are incredible !!! Thank you sooo much

[@soyuzssr]

Gary, It would really help if you could make more tutorials like this one. Especially on Numbers. This is super practical and useful !! Thank you indeed.

[@PhilipKingLondon]

Gary already has a stack of excellent videos on Numbers and everything else Mac-related.

[@macmost]

kzbin.info/aero/PL1bsBsPtN-9S2xQDWp43cwTcGhxPAR1Hp

[@desertpatient]

Thanks bunches

[@PhilipKingLondon]

I’m composing the text for a memorial inscription, which has a maximum of 36 characters per line. I was going to use a text editor, but with Numbers and LEN() it’s much easier to keep an eye on the character count. Very useful!

[@macmost]

You could also do it in Pages. It has an indicator you can turn on with counts.

[@PhilipKingLondon]

@@macmost True that Pages gives a character count for a selection, such as a selected line, but the advantage I found doing this in Numbers is that the count remains visible even when text isn’t selected. I made a two-column table, with rows for each line of text, a wide column-A for the text and the formula in col-B with a summary footer giving the total character count.

[@joeb8725]

When copying and pasting how do I set a cell to automatically change the font size? Every time I insert something it changes the font size to what it was and not what my table is set to

[@gwestlun]

Gary, how did you get the row at the top with length, count words, concatenate, etc? Love your informative videos!

[@PhilipKingLondon]

Those are separate sheets in his demo document. See the plus button at the left of that row? click that and you get a new sheet in the document, then you rename it whatever you like.

[@melissaharer9475]

This was so helpful! Is there a way to remove middle names? Pulling data and some people have middle names and so do not.. but we just need first and last name... not sure if that's possible?

[@macmost]

Yes. A variation of what I do in this video will get you there: kzbin.info/www/bejne/lZyxg2iubMuUmJI

[@LanzaroteShowcase]

Great, thank you. I use concatenate all the time but didn't know about the & function. How do I paste a list of filenames into numbers but still keep the numerical format the same time? EG: dog 01 looking happy.jpg dog 02b growling.jpg dog 03 wagging tail.jpg when pasted changes the number parts to dog 1 looking happy.jpg dog 02b growling.jpg dog 3 wagging tail.jpg note the zero missing but not in the case of the 02b I can't find a way to do it!

[@macmost]

Not sure what you are dealing with there. If I copy your "dog 01 looking happy.jpg" above and paste it into a Numbers cell, I get "dog 01 looking happy.jpg."

[@thevonmunchies9213]

Thanks Gary! Any idea how I could find and replace everything between 2 characters? I want to find anything between and delete it. I am trying to strip a whole bunch of useless code from a website i.e. needs to be Thanks!

[@macmost]

NOT in Numbers, I assume, right? You could do this in a variety of ways, but it will require some coding. You could use regex in apps like CotEditor or BBEdit to search for that text and replace it without the part between the quotes. But regex is definitely a skill you need to master to do that. Not sure if you have that in your toolbox. If not, it could be easier to just do it manually.

[@thevonmunchies9213]

@@macmost Thanks Gary, I was doing it in numbers on an CSV file. Will do a bit more research. Great channel!

[@himmatmangukiya]

How to sum weekly data in numbers, like I have day by day sum, and now I want to weekly data (sum) from this column.

[@SAFARI.FITNESS]

Good demonstration, sorry for the preview comment

[@macmost]

Not sure what you mean. The work IS how to do it. Try the examples I show.

[@SAFARI.FITNESS]

@@macmost yes got it now. thank you. and you got it a like a a subscribe

[@ebwerner08]

Your content is amazing and i have been able to learn a lot. But i have a question, Is there any formula that allows me to convert text/column from one language to another ? Like im Excel for example ? I would really appreciate your help with this. Thanks in advance

[@macmost]

No, there is no language translation function in Numbers.

[@macmost]

I don't see one for Excel either. Excel does allow you to translate text as part of the interface, not a formula. You can do that on macOS too in all sorts of apps, including Numbers. Select some text, like "Bonjour" and Control+click it and you'll see Translate as one of the options.

[@KhorneliusPraxxGames]

This will sound weird but...what if, for example, at 02:37, you want column C to read as it is but the name Ryan Middleton is Bold or Italic. Is there any way in Mac Numbers to have only some text in a formula modified in some way without having to format the entire cell?

[@macmost]

There's no way to use a formula to style only some of the text like that. What's your goal in doing that?

[@KhorneliusPraxxGames]

@@macmost no problem...it is hard to explain what I was trying to do. Project looks fine with all of the other skills you taught me. thanks

[@Maxx-Jay]

Is there a way to substitute text in a .txt file with text in a Numbers spreadsheet? I have placeholder text in a text file. I have a spreadsheet with columns for the placeholder text and the text that should replace the placeholder text in the text file. For example, the placeholders in the text file are RED, GREEN, and BLUE. In Numbers I have a column with RED, GREEN, and BLUE. In the next column I have the replacement text, ORANGE, PURPLE, and SILVER. So in the .txt file, RED should be replaced with ORANGE, GREEN with PURPLE, and BLUE with SILVER. I hope this makes sense.

[@macmost]

Not sure of your exact situation, but if the columns are separated with tabs and the rows with returns then you can copy and paste the text from a text file into Numbers and it will separate it out into cells.

[@michaelxavier230]

is there a text function format like in excel? instead of 05-10-2021, mmm-yy = May 21

[@macmost]

kzbin.info/www/bejne/rmWzi5WIpcR-ac0

[@michaelfischer2524]

How do you get numbers to add up the same word repeated in the column to show a total

[@macmost]

Sounds like you are looking for COUNTIF.

[@wassha09]

How do you make text vertical in the cells?

[@macmost]

There's no way to do that in Numbers.

[@drnasermdn]

can you help Trim trailing spaces in Numbers

[@macmost]

One way would be to use the REGEX techniques I show in the video. If you don't know the right regex code, do a quick search for "regex remove trailing spaces" and you should get some ideas.

[@auapplemac1976]

I set cell for Text. While the text will wrap within the cell, I want to us Return to get to the next line within that cell, but instead it cursor goes to the cell below. Can you stay within the same cell? If so, how?

[@macmost]

Option+Return.

[@fatihbicer7353]

Hi Gary, thank you for all your videos. What I am trying to do is, I want to change every second letter of the words in the cell, lets say "Hey Gary how are you" - "H2y G2ry h2w a2e y2u" is that possible?

[@macmost]

See my reply on my site where you asked (easier if you only ask in one place).

[@fatihbicer7353]

@@macmost Thank you so much for your reply Gary!

[@adlin61]

Can we rotate text within a cell vertically, like on excel! I really like numbers but I can’t seem to find that feature!

[@macmost]

No, you can't do that in Numbers.

[@marke2401]

@@macmost can a cell be rotated at an angle? Maybe slanted is a better word…

[@macmost]

@@marke2401 No way to do it, sorry.

[@dp_world99]

How can i rotate text

[@macmost]

Rotate as in display at an angle? You can't do that with text in a cell. You can do it with text in a shape or text box (design elements, not in tables).

[@RudyGOfficial]

Question! How do you extract the first letter of each word in a sentence while also keeping any symbols or acronyms? See example: Before: "A personal computer (PC) is a computer designed for use by an individual." After: "A p c (PC) i a c d f u b a i." Thank you!

[@macmost]

Off the top of my head, I don't know. But it can probably be done with regular expressions. Not sure if you are a coder or your skill level there though.

[@cabijista1]

Is there a way to paste text to the end of selected cells without overwriting the existing text in those cells?

[@macmost]

You just have to edit the text in the cell, go to the end (Command+down), and paste.

[@cabijista1]

@@macmost hi, thanks very much for the reply. if I’m understanding this right, that only applies to a single cell at a time. Is there a way to this across multiple selected cells at the same time? Also should mention I’m working on an iPad. Thanks

[@macmost]

@@cabijista1 Multiple cells. OK. You have to think dynamically. Well, if the current text is in cells in column A, then put the new text into column B's cells. Then in column C's cells, use a formula to concatenate A and B. You can leave it at that, or copy the C column and paste the "values" from there back into A.

[@cabijista1]

@@macmost ok yes great idea, I will try that now, thanks!

[@cabijista1]

Ok, slight problem. To use your example, I cannot select multiple cells in column C to be effected by the concatenate formula. If I highlight one cell and double click on my iPad.. the option for adding a new formula is there. But if I highlight multiple cells and double click, there is no option to add a formula.

[@markclason2717]

Can I rotate text in a cell?

[@macmost]

No.

[@justwandermaya]

Let's say you were looking for all the people named Ryan on your list... could you write a formula that highlights the keyword on the spreadsheet?

[@macmost]

A formula shows a result in the cell with the formula. It can't reach out to other cells and change them. However, you don't need a formula for this. Use conditional highlighting. kzbin.info/www/bejne/Y2Xan2ljm5maiMk

[@abhijithraju690]

How to make pop up menu in alphabetical order

[@sushilverma2634]

i am looking for formula that convert numbers to text (eg.100 into one hundred rupees or एक सो रुपये मात्र)

[@macmost]

No formula for that in Numbers.

[@sushilverma2634]

@@macmost but when we ask press option esc than it speak correct is there any other option plz plz

[@sushilverma2634]

@@macmost finally got it in clip tools video thank you

[@loisskiathitis8926]

A very useful video tutorial today. I’m so grateful for the visuals. Thank you, Gary! 👏🏻👍❤️