David Ringstrom

# dringstrom

Member Since: Aug 7th 2013

Columnist

Likes: 0

Thanks: 9

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

Accounting Advisors, Inc.

## My answers

If you had dates on every single row then you could use the Text to Columns feature on Excel's Data menu to convert this into two columns without using a formula. The inconsistency in the data makes the formulas a little more complex.

The following formulas assume the first record you listed above is in cell A1, and there are no 1., 2., 3., but instead that the first character is the 1 in 16 MAR 17.

Given that background, put this formula in cell B1:

=IFERROR(VALUE(LEFT(A1,10)),"")

The LEFT function extracts the first 10 characters from cell A1, and then the VALUE function attempts to convert this to a number. If the first 10 characters represent a date, the formula will return a number that you'll reformat using the Short Date format from the Number section of Excel's Home menu. The IFERROR function causes Excel to make the cell appear to be blank if no date is found.

Put this formula in cell C1:

=IF(ISERROR(VALUE(LEFT(A1,10))),A1,MID(A1,11,50))

This time we're using the IF statement to return one of two results based on a logical test. The ISERROR function returns TRUE if the VALUE function returns an error, or FALSE if it does not. If ISERROR returns TRUE then the entire contents of cell A1 are shown, otherwise the first 10 characters (which represent a date) are skipped and all of the remaining characters are shown.

This article offers several techniques that may help: https://www.accountingweb.com/technology/excel/how-to-recover-damaged-ex...

I'm going to make a distinction between data that you want to remove and text that serves as captions that you want to keep:

1. Select the cells that contain the data you want to remove (this can include formulas as well).

2. On Excel's Home menu choose Find & Replace, and then Go To Special.

3. Double-click on Constants. This step should select only the data cells from within the range you selected but not any formulas.

4. Press the Delete key to clear the cells but keep the formatting intact. Alternatively you could choose Clear, and then Clear Contents from the Home menu.

The Constants choice within the Go To Special dialog box allows you to select only cells that contain text or numbers within a given range that you've selected within the spreadsheet.

It's hard to say exactly why you're encountering this error without seeing your spreadsheet, but I don't think the problem is IRR-specifically, but rather an issue with your data. The IRR function usually returns #NUM! if it can't calculate an Internal Rate of Return. I wrote about #VALUE! errors a while back, so this article https://www.accountingweb.com/technology/excel/resolving-value-errors-in... may help you clear up the issue. Or feel free to elaborate further about your use of IRR and I'll be glad to see if I can help you get this resolved.

It's hard to give specific advice without seeing a sample of your data, but the first thing I'd try in your situation is to select column A, and then choose Data, and then Text to Columns. Choose Delimited in on the first screen of the wizard, and then click Next. On the second screen of the wizard I'm going to guess that either Tab or Comma will make the data pop into distinct columns. However, if it's the case that your columns are all evenly spaced, choose Fixed Width on the first screen of the wizard, and then on the second screen of the wizard place column breaks as needed.

It's hard to be very specific without seeing your actual data, but let me see if I can get you close to an answer.

First, the SMALL function in Excel will enable you to identify the second smallest value. Let's say that cells A1:A20 contain various numbers. The formula =SMALL(A1:A20,2) will return the second smallest value.

Once you have that in hand, you next need to identify the matches on the numbers. Once way to do so is to use a helper column in Excel with a set of formulas that compare each value the result returned by SMALL. If the SMALL function is entered in cell D1, then you could put this formula in cell B1 and then copy it down the column:

=A1=$D$1

This will return either TRUE or FALSE, with TRUE being instances of the value matching the second smallest value.

If you have something else in mind reply back with more details and I'll be glad to see if I can assist.

Stacey,

It sounds like you're using the File, Open command in Excel to open your CSV file. Doing so can indeed lead to tons of repetitive work.

The alternative is to go to a blank worksheet and in cell A1 choose Data, and then From Text. This will launch an Open dialog box from which you'll choose your CSV file, and then you'll be walked through the Text Import Wizard. Choose Delimited on the first screen, and then Comma on the second screen. On the third screen you can mark any columns that have leading zeros as text, and you can mark any columns don't need as Skip. Once you've made your selections, click Finish. Going forward, when you open your spreadsheet you'll be prompted to choose a CSV file. You won't have to go through the wizard, the data will simply be replaced. Click Cancel on the prompt if you want to keep the current data.

If part of your manipulation involves removing rows, this won't help you, as you can only show or hide columns. Microsoft Query is the more sophisticated approach that can give you total control over your CSV file, but the From Text command may do what you need.

Let me know if this gets you closer to your end goal.

David

Denis, it sounds to me that the columns are not hidden but rather have a minuscule column width that makes it appear as if they are hidden. Click the corner of the worksheet (above row 1 and to the left of column A) to select the entire worksheet, and then right-click on the first visible column, choose Column Width, and the set the width to 9 (or the width of your choice). If that does not work reply here and I'll suggest another angle.