Highline Excel Class 13: Text functions & AMPERSAND

[imran01924400917]

from wherei can get the excel sheets for parctice?

[ExcelIsFun]

@imran01924400917 here:

people.highline.edu/mgirvin/Ex­celIsFun.htm

[TastyAnchovy]

You are a legend. An awesome one. Nothing on Itunes or elsewhere on the web compares to what you have done and made available. Thank you Mike

[ExcelIsFun]

You are welcome!

[Hunyie1911]

A question in the REPLACE function here (used to extract First Name Only)...

=REPLACE(E14,1,FIND(", ",E14)+1,"") When I omit the space after the comma, the formula returns the same result, so that space is really unnecessary. Otherwise, you can always use =REPLACE(E14,1,FIND(" ",E14),"") if you know the first name starts right after the space, which is very commonplace. That formula is also a bit more simple and easier to understand.

Still, your Excel classes are wonderful. Thank you so much!!!!

[alwayzsic]

would concatenate make it faster? without formulas?

[ExcelIsFun]

I am not sure I understand your question. Can you re-state?

[alternativeblue123]

Thank you very much for your videos. They´ve been very helpful.

[ExcelIsFun]

You are welcome!

[JSA19882007]

how do i take the first letter of the first name and the first letter of the last name?

[ExcelIsFun]

For the data set in this video, try the formula:

=LEFT(REPLACE(TRIM(A14),1,SEAR­CH(" ",TRIM(A14)),""),1)&". "&LEFT(TRIM(A14),1)&"."

[JSA19882007]

@ExcelIsFun Thank you sir, you are a lifesaver

[ExcelIsFun]

I am glad that they helped!

[sameer482]

Hey Mr.Mike you are doing really a great job..

But i still don't get the difference between "Substitute" and "Replace" functions.. please could you help me out in such regard..Thanx A lot.!

[ExcelIsFun]

SUBSITUTE finds a specific character, removes it and puts a different character in it place. You tell REPLACE what start 'character position' and end 'character position' and it removes it and puts something in its place.

SUBSTITUTE finds characters.

REPLACE looks for character position.

[radcanvon]

Radcanvon again. Have found the solution to my previous query through #192. Text to column function under data ribbon solved issue. Now just have one column with first names. I am a total convert - your wonderful with the way you make excel so user friendly. Thanks

[ExcelIsFun]

You are welcome!

[radcanvon]

CRM database uses one field for first/middle name, so mail merge greeting of Dear June Estelle shows. Know how to extract first name if all cells are equal, but not all cells have more than one name (therefore no space) so when formula is applied doesn’t work for single name cells. I manually go into the single names and create a space and then the formula works for those cells. Any suggestions for single/multiple name combination rather than do the manual thing for each single name cell? Thanks

[ExcelIsFun]

I am not sure, But No Problem, Try THE best Excel site for Excel questions:

mrexcel[dot]com/forum

[aman1245singh]

Thanks again for the wonderful video sir. :)

[ExcelIsFun]

You are welcome!

I take it you are watching the whole Highline Excel Class? That is great - you can learn a lot!

Thanks for rating 5 stars each time you watch!

[hautti]

You are doing a great work Mike....!!May God bless you. :D

[ExcelIsFun]

Thanks!

[emailuznow]

Hi Mike

If have a large data with full names

e.g

Mike J Hunter

Norman-Price Smith

Harry. Redknapp

Prince-H, Hughes

and i want to seperate them via First name, Middle name and last name. As you can see the pattern to FIND is not always the same i.e some names have spaces after them, some have (Periods) after them and also commas or Dash.

How can i do a formula where im telling it to look out for a period/space or a dash.

Hope this makes sense

[ExcelIsFun]

See e-mail I sent.

[dannybro]

Hey Michael how can i return a true or false value if a text or number is found in a cell ? 10x again :D

[ExcelIsFun]

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.

[dannybro]

ok 10x .... i think it also works with isblank(cell) .

anyway it was about managing my holidays and it worked :) .... i was trying to do something like this : =IF(ISBLANK(D8);C7;(B7+C7)-F7+­ABS(DATEDIF(D7;D8;"m")*2))

[tongzilla]

no haircut dammit.

[ExcelIsFun]

OK. How about a diet, then? TRIM is very versatile.

[thirthyest]

omg !!!!

this one is a bit tough for me :)

[ExcelIsFun]

Tough is good because it means you can learn a lot!!!

[Thunderbird2014]

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 { }.

[ExcelIsFun]

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

[Thunderbird2014]

I saw that video. I was just wondering if that can be applied to the SUBSTITUTE function.

[ExcelIsFun]

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