Sunday, 24 January 2016

Excel Formula : Convert a text to Number

Got a Excel file from other resource witch Column A is set as text. At the tail and start of number there are some spaces filled in, for example cell A1 is '  4 '.

My challenge is to convert whole column to numbers which can be used to do sum or other math calculation.


With some research, I constructed this formula for a new column :
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))

it perfectly resolved this challenge as you can see from following screen shot.

Note: trim will not work with the only space cell. Thanks reply from Hari Krishna.

Excel Formula: Search a Column of Strings to Match Another Column

Once a while, I have to work on Excel sheet manually. Today I have to search exported hundreds of ip addresses in a pre-defined excel spreadsheet to see if there is a match. It seems a easy work, but it took me almost an hour to find right formula.

Column O includes all exported ip addresses. I have to manually search if those column O's ip addresses are appearing in column B's text. If yes, which row is it?

Formula is set at Column N.

=MATCH("*"&(O6)&"*",B:B,0)

As you can see from cell N6, the number is 7, which means the text in B7 includes O6's string.
N12's number is 5, which means the test in B5 includes O12's string. 


Microsoft Office Tips and Tricks (Word, Excel, Visio, PowerPoint)

Online PDF to Word


1. Word
1.1 Convert Texts into a Table:


 



2. Visio
2.1 Find & Replace


3. Excel
3.1 Open Excel files in New Window
Lots of times, I will need two Excel windows side by side, on different monitors, so I could work on both at the same. By default, Excel will open Excel files into same Excel Instance and you will have to split window or re-arrange excel file in same monitor to see both files. Here is small trick to change this behavior. 

  • In Excel 2003, go to Tools -> Options -> General tab. Make sure the option, ‘Ignore other applications’ is checked.
  • In Excel 2007 & 2010, Click the Office button -> Excel Options -> Advanced. Under General, check ‘Ignore other applications that use Dynamic Data Exchange’.






3.2 Formula- Convert a text to Number:
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))

3.3 Formula - Search a Column of Strings Based on Datas in another Column:

=MATCH("*"&(O6)&"*",B:B,0)

3.4 All Kinds of Excel GIFs
Automatically Add Column Titles on Each Print Page:

 

Set Tables Border:
 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 



用数据透视表做分组计数:





Reference:
1. Excel Tips Net
2. Excel Formula : Convert a text to Number
3. Excel Formula: Search a column of strings in the spreadsheet