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?
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.
I have 5 columns of data where each column of data has two number in it separate by a space where the headers for each column is c1, c2, c3, c4 and c5. for example
where each of these number pairs is under a separate column. The preview option for this forum editor is showing quite a difference between intended presentation and actual..
What I am looking to do is for each line item is to put the content of each row into a single cell with vertical alignment of the pairs of numbers. for example c6 1 1 1 2 2 2 2 1 1 1
3 3 3 4 4 4 4 3 3 3
where each group of five pairs is in a single cell.
I am looking to do this in as automated an approach as possible. I dont want to have to ctrl-enter for example 4 times for each cell in c6 for 1000 different line items..
formula to separate information in a single cell into multiple columns. I have hundreds of rows of data, so a formula to quickly separate the information into individual columns would be good. Below are a few lines of data.
ALMADEN STATION* 900 227 168 182 176 OAKRIDGE STATION* 900 276 332 362 349 OHLONE‐CHYNOWETH STATION* 900 469 480 522 504 ALUM ROCK STATION 901 947 925 1007 972 BAYPOINTE STATION 901 181 196 213 205 BONAVENTURA STATION 901 137 140 152 147 CAPITOL STATION 901 650 680 741 715 CONVENTION CENTER STATION 901 788 814 886 856 CURTNER STATION 901 569 571 621 600 ORCHARD STATION 901 87 94 91
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$)
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 [30] 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 need to create a Macro that will link Horizontal data to vertical data in a separate sheet. Here's the Example:
I have data in a forecast spreadsheet that is horizontal, So, J2:U2 (12 columns for 12 months). This row of data needs to be linked in a single column of data on a separate sheet called "Price_Quantity". So I need C3:C14 (in price_quantity sheet) to be linked to the data in J2:U2 (in forecast sheet). Then C15:C26 needs to be linked to J3:U3, so on and so forth. 12 rows linking to 12 columns. All the way until C4959:C4970 is linked to J415:U415.
I can't seem to figure out to easily aggregate & automate a series of weekly values (ie. forecast volumes by a specific part number) into vertical monthly summaries, by product.
Here's the before picture:
column A column B column C column D column E column F Part num 1/1/2013 1/8/2013 1/15/2013 1/22/2013 1/29/2013A 100 100 100 100 100B 150 150 150 150 150C 200 200 200 200 200D 250 250 250 250 250
The output after manually doing this would look like this:
column A column B column CPart num Date QuantityA Jan-2013 500B Jan-2013 750C Jan-2013 1000D Jan-2013 1250
I want to separate them into several cells. Each cell can only have 30 characters or less. You can not cut it in the middle of the data.
After your separate B1 should be "C102, C110, C114, C116, C118," (30 characters) C1 should be "C120, C125, C128, C130, C131," (30 Characters) D1 should be "C132, C134, C135, C139, C140,"(30 characters) E1 should be.......... till the end.
I try to several functions too. But it does not works.
Is it possible to that the contents of a single cell and and place text of that cell into separate cells? From a web output I have some like the following in my first cell
asdfsdf|adkjoi|fdf234sd|fsf3ie43|||asdfjlkei393|dfjvie|d||adfjei|...goes on with 28 "|"
The text is in A1 and I would like it to take the first string of characters up to the | and place it into cell A2
Then take the next set of characters up to the next | and place into cell A3 and so on.
Once the cell in A1 has been completely separated, go down to the next row and do the same until it reaches the end of the data.
Normally I would create a text file with the web data and then import that in with "|" delimited fields but I don't want to create that extra step for the user.
I'm trying to separate text from numbers into two separate cells...
Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.
Input: Output 1: Output 2:
Col A Col B Col C Wells 123 Wells 123 Wells 1234 Wells 1234 Wells Fargo 123 Wells Fargo 123 Wells Fargo 1234 Wells Fargo 1234 Wells Fargo Inc 123 Wells Fargo Inc 123 Wells Fargo Inc 1234 Wells Fargo Inc 1234
Ideally, I would like to do this with a formula...
I need to get the string GE#### before each " class="button"> and copy it on a cell on the right. There are other links of this format [URL] ..... in the cell, but I am interested only the ones that have " class="button"> after it.
The length of the ID after GE can be 2, 3, 4 or 6 characters long. But I am ok with getting GE + 6 characters following it as that means I would get something like GE12" cl and I will delete the extra character by doing a find/replace.
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.
Is it possible to have a single userform put data into two separate sheets within the same workbook.
I have a userform set up for entering client data into a quote register,with no problems ( sheet 1)
A1, A2, A3, A4, A5, A6, A7, A8, A9
What I would like is for the data to also go to sheet 2, where I have a form set up to print off for our technicians to take on site.
The problem will be that I would like the data A1, A2, A3, A4, A5, A6, A7, A8, A9 not to store on this sheet after it is printed, so the next set on entries , can be printed etc.etc
I've got several Excel files that need to be edited separately. Once they have been edited, I need to paste the content of each file in a single separate Excel file.
Is there a way to retrieve the content from all the files in a folder, and merge it in a single file?
I have about 20 choices I'd like a user to select from and have the text output to a single cell.
Just create a Data Validation List? Yeah, but that only allows you to select ONE of the items in the list -- I want to select as few as zero and as many as all 20, combining the resulting text into a single string - space or comma delimited.
My first thought was checkboxes, but all I can seem to figure out is how to get ONE of the results into the cell, meaning I'm better off with the Data Validation List because it's easier.
Especially since I need to repeat this for as many as 200 or 300 lines,
How can I merge into a single cell from a range of selected cells that contain text. If the range of cells do have empty cells it should be smart enough to skip over or not to create an additional space. Any idea how can this be done? Macro or formula?
I need a function that will use a column of text values and test these values to see if one or more of the values exist in a single cell. If it does I need the function to return true or false.
Ie. cell A1 contains the text "Jim Smith" the B column contains the test names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim". Because Jim is in the cell A1 I would need the function in C1 to return the value "true". If A1 contained the text "bob smith" then function in C1 would return the value "false".
I'm trying to put both text and cell value from another sheet in a single cell.
I've tried: ='sheet'!cell"text" Doesn't work.
Also, I understand this is somehow it's supposed to be done ="Total: "&A12 But I need the cell data to be from another sheet and also I need the text to succeed the cell value.
Going by the above example, I tried: ='sheet'&cell"text" But that also doesn't work.
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 cell that has a formula in it like PREM + PRDCMP + ACQ + DLR in a spreadsheet that looks like this:
PREM PRDCMP ACQ DLR VIP COMM TOTAL 2 1 3 4 6 5
PREM + PRDCMP + ACQ + DLR 2 1 3 4 10
where PREM + PRDCMP + ACQ are colored red and + DLR is colored blue. This formula is entered in that particular cell as a text string. I have a formula in the PREM column that says if the word PREM is located in the formula to the left (and it is), then put the value that is located right under the PREM column (2) in the blank cell next to the formula. So, in the above example, I have an IF statement that sees that PREM is in the formula to the left and so underneath the PREM column, next to the formula, it would place the number 2. Now, I need Excel to detect if any word in the formula text string is colored blue and if it is, to put a zero in that column. So, in the formula above, if + DLR is the only text that is blue, I want to have Excel add 2 + 1 + 3 only. I want to have Excel take any string out that might be colored blue, such as + DLR and return the value of zero for it. The purpose of the different colors in the formula is to distinguish which information is relevant and which information should be ignored. So, I'd like Excel to read the above formula and say put the PREM amount in the cell under PREM, put the PRDCMP amount in the cell underneath PRDCMP, put the ACQ amount in a cell under ACQ and put zero in the DLR column since it is colored blue. If any of them (PREM, PRDCMP, ACQ, DLR, VIP or COMM) are colored blue, I want Excel to put a zero in that column.
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
I've got problem with combining 3 cells into one. First cell contains text, second have date yyyy-mm-dd and last one is as previous. I'm using formula .
[Code] .....
And I'm getting: TEXT_41694_41701. How can that be corrected to use formatting for date format to get TEXT_yyyy-mm-dd_yyyy-mm-dd?
