For A Column Of Cells Separate Text In Single Cell Across Multiple Columns
Nov 29, 2013
New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:
(NOTE: does not contain actual names or info) Book1.xlsx
This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?
While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.
Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type  in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.
I'm trying to work out how to take all cells with data from multiple columns and stack them in a single column.
Here's the history...
I have multiple part numbers in single cells in column A. I perform a text-to-columns function. The resulting part numbers spread across multiple columns (say, B through K). Now I need to get all the part numbers, in their own cells, stacked in column A for one continuous list of single cell part numbers.
Is there a VBA option for cutting only the data cells from Column B-K and pasting the data at the bottom of column A while avioding blank cells?
I have been sent an export from Outlook in a .csv file. The Body of the Email is all contained in one cell for each row (each email detail) and is in the following vertical format within the cell:
Column Header blank row Data blank row Column Header blank row Data blank row
....and so on. I've tried to do something using Transpose and Text to Columns, but I've not been able to successfully seperate out the data into a useful format in Excel. The data are responses to a website and contain Account/Contact information which I hope to be able to import into a CRM system as new Leads. way of extracting this information, so that I can get each row into a format that gives me the column header with the related data below, as you'd normally expect to see in Excel?
I am creating a Spreadsheet to work with a game which I am designing. I want to create a combat calculator which will generate formulas based on which class a person chooses to be, and their choice of weapons, armour and other equipment (makes things a lot easier for me to visualize). I have created the first worksheet which will act as the interface where users can input their character configurations and a second worksheet where all of the data is stored, included items and their values.
Assuming that on the interface I have cell A1 which will let the user select their class (Warrior, Archer, Sorcerer). I also have cells B1 = Helmet, B2 = Body Armour, B3 = Leg Armour, B4 = Feet Armour, B5 = Hand Armour.
I would like cells B1:B5 to create a Data Validation based on the class which is selected in A1.
E.g. IF A1="Warrior"; B1 = A list of Melee Helmets, B2 = A list of Melee Body Armour... etc IF A1="Sorcerer"; B3 = A list of Magic Leg Armour...
I have managed to do this with the following formula, but only if the data is stored in the same sheet. Cell B1 =IF(A1="Warrior", =meleeArmour1, =IF(A1="Archer", =rangeArmour1, =magicArmour1)) Cell B2 =IF(A1="Warrior", =meleeArmour2, =IF(A1="Archer", =rangeArmour2, =magicArmour2))
[NOTE] =meleeArmour1 represents the range of cells where the Melee Helmets/Head Armour is stored. =magicArmour2 represents the range of cells where the Magic Body Armour is stored.
The group cell names do not seem to work if they are on a different sheet, nor does it work if I replace the group cell name (=meleeArmour1) with the full formula (=Sheet2!A$1$:A$10$)
I have a long column of info as below. I want to extract (the name changes)- "SEAPORT TWN" etc and also "01.30" and place into separate columns say G and H at the next available cell.
** SWITCHLIST FOR TRAIN---Grain Spec -East DEPARTURE TIME from WESTTOWN is 01:00 TOWN STOP---SEAPRT TWN Arriving at 01:30
PICKUPS Terminal Shipping Burl North 460020 GRAINCAR empty Elevator Co. Ltd. Terminal Shipping Sante Fe 100396 GRAINCAR empty Elevator Co. Ltd. Train should leave this town with 2 car(s) TOWN STOP---PRARIE TWN Arriving at 02:00
SETOUTS Elevator Co. Ltd. Burl North 460020 GRAINCAR Grn w/Wht Lttrs -4 Bay Elevator Co. Ltd. Sante Fe 100396 GRAINCAR Maroon w/Wht Ltr -3 Bay 1/05/2007 8:57:52 a.m. ...............................
My starting point is a big data set that needed to be validated by several users. I split the data set into smaller sets that were sent out. All users have filled in the same column called "Input"
What I would like to be able to do is to paste their input into a single column on my original data set and keep the name of the input next to it. Is it possible to paste without overlapping prexisting data ? Something of the likes of F5 ("Select blank cells/text") ?
I've attached a simple file with dummy data to explain myself.
How can you move data from multiple columns into a single column? I have attached a short example of the data I receive. (In reality, the data could be hundreds of columns and/or hundreds of rows, but this is representative of what I might receive). It does not need to be sorted and including the header row in its own column is optional. I do not know VBA all that well but can muddle through it if someone can give me an example of what the code should look like.
in moving data from multiple columns to a single column. I have attached a sample image from an excel file which details the requirement. The first column contains a qualifier, that should remain constant when the data from columns B to the end move to a single column "B". The number of columns for each row is different, however, there is a maximum number, say 25. As mentioned in the image, when the data from columns B to the end is moved in to column B, column A is retained fixed, to the original value, and the original data below it is pushed below. Any pointers to how this can be achieved by VBA or without VBA?
I have 300 rows worth of data that looks similar to this, all organized in one column:
John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Snider Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555
As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.
John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555
Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.
I saw this thread from June Cut & Paste Macro: Move Address From Column To Row and I think this what I would like to have my macro do. I have one column which is copied and pasted as text in excel and there is several blank rows.
I attached the spreadsheet example - it has 40 lenders, with the top row being the lender name (A1), followed by address(A2), city-state (A3), two blank rows(A4-A5), lender type (A6), approved date (A7), one blank row (A8), lender specialty (A9), telephone (A10) , e-mail address(A11) and two blanks rows (A12-A13) and A14 starts over again. For this example there should be 8 columns and 40 rows (lender name, address, etc...). As I mentioned I think the previous thread's macro would work with some minor tweaks. I couldn't figure out the best way to handle the blank rows and or remove the blanks and what to add or take out of the macro code.
I have a long list of data with many columns and I'd like all the information to be in one column without manually copying and pasting each column and adding to the first column. The data has different amounts of rows and columns as well. An Example is below. I'm using Excel 2010. Is there a formula or something for this? This isn't the data I'm using but just an example since I do this frequently.
I have a single rangle of numbers in Column A, and in Columns B and C and want to list all possible combinations of these numbers. If there is a possible solution without using a Macro I would perfer that, but if not I'll take what I can get.
I have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:
The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.
A B C Row 1 November Row 2 1 5 15 Row 3 1 0 2 Row 4 0 1 3
November is in A3
If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
Project Benefit Type Delivered or Enabled Benefit
PJ1 Financial Delivered Saving of $4M over 24 months.
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
Financial - Delivered Financial - Enabled Tech - Delivered Tech - Enabled Green - Delivered Green - Enabled
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
I've managed to write a macro to take data input into a column-oriented form and transpose it into the next available rows in 3 data sheets. In the blank workbook, you will find a functional data entry form with a "submit" button that transposes data from the first column of the form to the next available row in sheet "Data_R", data from the second column of the of the form to the next available row in sheet "Data_I", and the sum of both columns to the next available row in sheet "Data_C", clearing the form for further use.
Now, I need adding to the macro code that will sum data from the constituent columns of each multi-column category header (Marketing, Intake, etc.) in sheet "Data C", entering the sums under their respective single column headings in the next available row of sheet "Data_S".
I need to do this in order to use Defined Names (utilizing the OFFSET function) to create charts comparing the different categories that automatically update as new data is input via the form. I know how to do this, I just need the multi-column categories summed into single columns to make it work.
I have a sheet labled Parsed and a sheet labled Prices. The Data on the Parsed sheet is in W2:W1000. the data on the Parsed sheet, can have anywhere from one value to up to ten values in it. (again all sparated with a space.) the data is separated by a space between each number value. I am trying to find a way to take the values from Parsed!W6 for example and separate the data into the prices sheet in to different columns. Some cells may be blank.
I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.
If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.
What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".
So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.
So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.
To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks).
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I have values on master sheet and want to link those values to source documents in the same workbook. But the values on master sheets are made up of more than one value on another sheet.
For example on master sheet I have $137,000 that is made up of values in P7 + Q7 + P57 + Q57 on source sheet. If user wants to see where this number is coming from; the user will click the number to go to source document.
On a single valve I linked to the source sheet to a single cell. One thing I was thinking, using conditional format, so if one of the cells is selected in source document the other cells that combine the total of the master sheet cells should be highlighted, just my 2 cents.
I'm trying to take the sum of multiple cells and return it to one single cell using the Vlookup
For Example I want to match the ID numbers from one spreadsheet and use the list of codes to return the value of the sum of all matched numbers. So in this example I would want the number 65 in one cell...