Hi all. I have a giant Excel file, that I’ve been looking at in Numbers. It has a TON of tables. My question: is it possible to “bulk” organize these tables if they have cells formatted like this (each cell looks something like the following)?
Address: One two three street Title: Some Title CEO Fax: xxx Site: somesite.com Etc…
In other words, is there a way to split cells that are formatted this way, or am I going to need to do it manually? I’m open to using programs other than Numbers, if necessary.
Hi, thanks, this looks very close. I’m thinking now that I should have been a little more specific. Some of the colons and characters have an inconsistent number of spaces after them, so this kind of mucks that up.
I’m wondering if there is a way to bulk scrub/format these for pre-setting the text to columns
In the past I have been able to extract data and reformat it to a useful database with FileMaker Pro. If needed you can export the organized data again in Excell format.
As a first attempt, you could use search and replace to replace two spaces with one space. You might nd to run this more than once, in case there are, say, three spaces. Whether you can do this in excel, I don’t know. You might need to export to csv, then use an editor such as TextMate.
Second method would be to export to csv, then use an editor such as TextMate to perform a regular expression search and replace. This allows you to specify what kinds of characters you expect in certain places, capture those that match, and replace the original text with the captured text. You can Google ‘macos regular expression app’ and find various tool to help define the regex (regular expression). TextMate, Sublime text, and other programmers editors can be used to do these substitutions.
Third option just discovered: apps that clean up text for you. There is Clean Text, probably others. I have no experience with these, as I usually use TextMate, or commands in Terminal to clean up text.
If you always want to reduce multiple spaces down to one, you can use something like substitute(" “,” “), and nest them even; so getting 3 spaces to 1 is: substitute(substitute(“xzy”,” “,” “),” “,” ")
Other methods are better, but this is low-tech and works.