Category: Texts and Strings

Count how many times letter found

March 3, 2017 ANmar

Found this in an old file while doing some digital cleaning This will show you how you can easily count how many times a letter (or any string) into a cell. So the main formula doing that is … =LEN(I12)-LEN(SUBSTITUTE(I12,”o”,””)) Of course, this assumes you are looking to find how many the letter “O” is…

Formulas - combined functions, Len, Standard functions, SUBSTITUTE, Texts and Strings

Multiple lines in cell using functions

December 2, 2016 ANmar

You must already know that when you press Enter after you modify a cell, the cell content will be saved and you jump down to cell below (we can change that behavior by the way, but that is another post).

And you could have already know that if you want to have multiple lines inside a cell, you need to press ALT+ENTER to do that.

But, how can we do that in formula ??

I got a request on how to do that in a spreadsheet, so, the client wanted to convert two columns having two parts of address, Column C has Street address and street name, and column D has city, state and Zip code.

CHAR, CONCATINATE, Formulas - combined functions, Standard functions, SUBSTITUTE, Texts and Strings

Extract current (Active) workbook name

September 14, 2015 ANmar

I need that more often than I thought It is basically extract the full workbook name that we are in, with no folders, and with no extension =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-6) So, here it is…. And if we want to go 1 step further, I often have the version of the tool as part of the filename,…

CELL, Formulas - combined functions, Information, Lookup and References, MID, SEARCH, Standard functions, Texts and Strings

Get column name (columnname as A,B,C, etc) as input inside cell

April 6, 2015 ANmar

Excel already has the formula ROW()
=Row()
Which as we may already know will give us the number of the row we are in, if you add no parameters

However, the COLUMN() is kind of tricky

Because of the fact that cell addresses (or references) are based on the old style of R1C1

=Column()

ADDRESS, CHAR, COLUMN, Formulas - combined functions, LEFT, Lookup and References, ROW, SEARCH, Standard functions, Texts and Strings

iframe in Excel (XLiFrame)

February 19, 2012 ANmar No Comments

This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too.

Formulas used to bring actual data is:

=IF(OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN())="","",OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN()))

While formula used to bring header labels:

ADDRESS, COLUMN, Formulas - combined functions, IF, INDIRECT, LEFT, Lookup and References, OFFSET, Standard functions, Texts and Strings

Zipcode-State search

February 12, 2012 ANmar No Comments

Enter a zip code, and Excel will tell you in which state it is
It also has a list of all zip codes for all states and a technique (INDEX and MATCH functions) to get the state from a given zip code.
Simple file to help learning INDEX and MATCH

The formula used is:
 =INDEX(Data!$D:$D,MATCH(TEXT(Search!C4,"00000"),Data!$C:$C,0),1)

Extract Address into table

February 10, 2012 ANmar No Comments

Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table

Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above

Which is usually what you got from any list of addresses online, and you want to convert it to table.

This is exactly what one of clients had and needed, so here is the file that does that.

Basically you need to have 5 columns with formula in column G as:

=OFFSET($B$1,(ROW()-5)*4+4,0)

to extract first line (Starting from cell B5

Then in column H as:

Formulas - combined functions, LEFT, Lookup and References, MID, OFFSET, ROW, SEARCH, Standard functions, Texts and Strings

Insert Blank rows into table using functions

February 5, 2012 ANmar No Comments

Here you will see how to work with functions to insert a blank row every certain number of rows in a table using a blank sheet to copy all values of that table into the new sheet Then using Copy > Paste Special to make them constants The new thing is that this is all…

CHAR, COLUMN, Formulas - combined functions, IF, INDIRECT, INT, Lookup and References, ROW, Standard functions

Progress bar in pure functions

January 29, 2012 ANmar No Comments

This one is very similar to Tally chart, however, it is for another purpose. It is used to draw a progress bar, yes a progress bar in pure functions, using the formula: =REPT( CHAR( 8),INT( D2/10))&REPT( CHAR( 7),INT( D3/10)-INT( D2/10))

CHAR, Formulas - combined functions, INT, REPT, Standard functions, Texts and Strings

Tally chart in functions

January 26, 2012 ANmar No Comments

Using a single function can make a graphical view to your number Function is REPT along with IF and INT to show that number in graphical view, formula used here is: =IF(B4<5,REPT(“|”,B4),REPT(REPT(“|”,5)&”-“,INT(B4/5))&REPT(“|”,B4-INT(B4/5)*5))

Formulas - combined functions, IF, INT, Logical, Math and Trig, REPT, Standard functions, Texts and Strings

CamelCase to Camel Case

January 26, 2012 ANmar No Comments

Here is a good practice for combining functions to get a full smartsheet It basically add a space before any capital letter in a text, helpful when you have strings without spaces and want to convert them. It is all in formulas. Put additional spaces before any upper case letter. made specially for custom request….

Read More “CamelCase to Camel Case” »

CHAR, FIND, Formulas - combined functions, IF, Information, ISERROR, Logical, Math and Trig, MID, SMALL, Standard functions, Texts and Strings, TRIM