Forum Discussion
Solved
Stop automatic text-to-columns formatting
This morning I was pasting text into excel and using Text-to-Columns to parse it. Later, in a different file, I was pasting other text that I did not want parsed into different columns. But excel is a...
I'm sorry but i see no limitations which could prevent to use nested IF. The only point it is a bit less flexible compare to nested If equivalents.
But what to use depends on goals, in some cases quick hardcording works quite fine.
If use formatting nested if becomes much more clear and editable. Like this
=IF(ISNUMBER(SEARCH("Sales", B3,1)),"Sales",
IF(ISNUMBER(SEARCH("Arch", B3,1)),"Architecture",
IF(ISNUMBER(SEARCH("Land", B3,1)),"Land",
IF(ISNUMBER(SEARCH("All", B3,1)),"All",
IF(ISNUMBER(SEARCH("Contracts", B3,1)),"Contracts",
IF(ISNUMBER(SEARCH("Construction", B3,1)),"Construction",
"No Match"
))))))
If instead of hardcoded strings use references nested IF becomes more flexible. And if add some extra references (nested if) for future strings to find it becomes even more flexible.
=IF(ISNUMBER(SEARCH($F$1,B3,1)),$G$1, IF(ISNUMBER(SEARCH($F$2,B3,1)),$G$2, IF(ISNUMBER(SEARCH($F$3,B3,1)),$G$3, IF(ISNUMBER(SEARCH($F$4,B3,1)),$G$4, IF(ISNUMBER(SEARCH($F$5,B3,1)),$G$5, IF(ISNUMBER(SEARCH($F$6,B3,1)),$G$6, IF(ISNUMBER(SEARCH($F$7,B3,1)),$G$7, IF(ISNUMBER(SEARCH($F$8,B3,1)),$G$8, IF(ISNUMBER(SEARCH($F$9,B3,1)),$G$9, "No Match" )))))))))I don't vote for nested IF, i would like to say where is no limitations here. What to use that's concrete person choice.
Sometimes, Excel is too clever for its own good.
Select an empty cell.
On the Data tab of the ribbon, click Text to Columns.
Select Delimited, then click Next >.
Clear the check boxes of all delimiters.
Click Finish.
The problem should be gone (until the next time...)
Selected an empty cell, then clicked Text to Columns ... ERROR: no data selected to parse. I really need Excel to stop auto Text to columns my CSV files as I happen to need leading zeros like it is in the csv file.
The Import from CSV file doesn't do anything different. It imports into a window where its already converted a text field with leading zeros into a number and you can't change it. The only thing you can change is if the delimiter is a comma.
Any help setting up Excel to just display the csv file in column A like it did the first time would be appreciated, thanks.
P_A_R_K_Y : Just type something into an empty cell, do the Text To Columns with a strange character like a tilde (top left corner of keyboard). THEN bring in your CSV data.
Jeff_Stubing First off, thanks for replying so quickly. Just so I'm understanding correctly. To stop excel auto formatting a csv file incorrectly. I (open a blank workbook?), then type something into an empty cell, then click the text to columns, (use delimited) and put an strange character like tilde (~ found elsewhere on keyboards outside the US), and then "bring in" your csv data. By "bring in" do you mean to select, from the data menu, "from text/CSV" and import the csv file? This doesn't work, hence why I'm putting my assumptions in brackets as I might have misunderstood your steps.
So I've seen this solution in two places, but when I try 'text to columns' on an empty cell, I get this error message: "No data was selected to parse"
- Ok so I have to put a space in that cell first to do that. Ok - thank you!
MysidEmq you can do it on any cell with a something in it. Because you are trying to clear all settings from the text-to-columns and you don't need to click on "finish", it won't matter where you do it.
After you just paste your data and it will not show you multiple columns.
HansVogelaar
you don't need to click FINISH.... you can just press ESC (it feels weird but it works)Your solution started to work(!thanks) but I no longer can remove unwanted delimiting bars. As soon as I select 'space' as a delimiter it separates first and last names into separate columns.
It doesn't have to be space, you can set up whatever delimiter you want, just set it to something that isn't going to come up in your data. "|" is popular, or "\" or "%".
As soon as I select 'space' as a delimiter it separates first and last names into separate columns.That is to be expected. If you select Space as delimiter, all spaces will act to start a new column.
HansVogelaar Thanks so much for your answer! This has been a frustration for me for years! FYI, it would not let me even open the Text to Columns dialog with a blank cell selected, but it worked fine with a populated cell selected. Thanks so much!
Is there any way to turn it off for good? Or will I need to do this every time I've used Text to Columns?
Excel will remember the settings during a session, but when you start Excel, there shouldn't be presets.
I always do the splitting a file thing first, then other comparisons later in the next process. But this way of turning it off, while awkward, works without me having to exit Excel and re-enter.
- Thank you, I've needed this for ages!
