See how to transform data into useful data using formulas and Text functions such as; TRIM, PROPER, SUBSTITUTE, REPLACE, FIND, and LEFT. See how to use the ampersand and the CONCATENATE function to join data. See how to speed up slow calculating spreadsheets with a concatenated column that joins three criteria and makes conditional (criteria) summing (adding) quicker. See how to: 1) create upper case for the first letter of each word, 2) extract only the last name, 3) extract only the first name, 4) get rid of extra spaces, 5) sum with 3 criteria using the ampersand and the CONCATENATION function. This is a beginning to advanced Excel class taught at Highline Community College by Mike Gel excelisfun Girvin Busn 214 btech 109
{ 11 comments… read them below or add one }
ok 10x . . . . i think it also works with isblank(cell) .
. . . . i was trying to do something like this : =IF(ISBLANK(D8);C7;(B7+C7)-F7+ABS(DATEDIF(D7;D8;”m”)*2))
anyway it was about managing my holidays and it worked
Try this:
=OR(ISNUMBER(A1),ISTEXT(A1))
If a number (1. 25, 12, 0, -12, etc. ) is in A1 the formula says TRUE
If characters (word, $, *, etc. ) are in A1 the formula says TRUE
If a blank, an error or a TRUE/FALSE is in cell A1 the formula says FALSE.
Hey Michael how can i return a true or false value if a text or number is found in a cell ? 10x again
OK. How about a diet, then? TRIM is very versatile.
no haircut dammit.
Tough is good because it means you can learn a lot!!!
omg !!!!
this one is a bit tough for me
Dear Thunderbird2014,
Yes, the SEARCH function can handle arrays. This video shows the SEARCH with an array from cells (not typed in with syntax):
Excel Magic Trick 290: Count Codes From Column of Text Strings
–excelisfun
I saw that video. I was just wondering if that can be applied to the SUBSTITUTE function.
Dear Thunderbird2014,
{1,2,3} (numbers in columns) or {1;2;3} (numbers in rows) uses array syntax to enter three numbers into an argument. in this video we are not doing that, but others I have, such as this one:
Excel Array Formula Series #5: SUM 3 Largest Values
–excelisfun
Great video. At 6:27 regarding the instance number in the SUBSTITUTE function, could you enter instance 1 and 2, or maybe 2 and 3? I think another video had something similar where you used { }.