I have a list of 2500 companies. In each cell there is the company name, street address, and phone number. And of course, each one is of varying length. I need a way to put the company name, street address, and phone numbers in seperate columns. I tried going to text to columns but it wouldn't work due to the varying lengths.
View 5 RepliesSeperating TEXT in different columns placed at non-fixed location in a cell...
I have some data which contains people's name and the places ( i.e Cities) mixed which I need to seperate, however the challenging part there is no consistency in data as the city can appear first in the middle , last or even just the city name in the cell but it needs to be seperated ...
In Column B I have the following type of DATA ...
I want to separate the text into columns as in Table
Name
Name
Contract
Ref. No
[Code].....
I have to separate text & numbers in different columns.
e.g. 349,910 American Capital Ltd. (a) 4,902,239
i want all 3 data in separate columns, there is a series of data like this wherein numbers are there both at begining & end & text in middle.
I have a field that contains rows of data, each made up of a Number followed by Text. The length of the numbers differ - sometimes 4 digits, sometimes 5 or 6. Similarly, the text differs by word-count. An example is
2546 Nags Head
75698 Dog & Duck
634 Crown
I want a way of dividing the numbers from the text (numbers in one column and text in another).
Standard 'Text to Columns' won't work: I can't use 'Fixed Width' due to the number-length varying, and I can't use 'Delimited' and [space] as it will then split all the text up word-for-word (concatenating them back will take ages as my list is 480 rows long).
I have rows of cells with the following information..
Row 1, Column 1: Address City/State
Example: 111 Old Creek Road Stanton, VA 25523
Is there any way to separate the Address and the City/State? I would much rather the spreadsheet be in the following format.
Row 1, Column 1: Address
Row 1, Column 2: City/State
or ..
Row 1, Column 1: 111 Old Creek Road
Row 1, Column 2: Stanton, VA 25523
I'm just wondering if there's a fast way to do this, or if I have to go in and do it manually - the reason i'm looking for a faster way to do this is because I have approximately 3000 rows to do it to. :shrugs:
I can’t seem to work out how to pull data relating to a particular title from one spreadsheet, to another, separating the information on.
For example, column A lists names, and column B lists tests scores for each person. However each person has done several tests, and the tests are listed in one cell, separated by a carriage return (not comma etc, so can't do a delimited function).
I want to create a new spreadsheet with names still in column A, but the test scores separated. So Column B = Test one, Column C = Test two, Column D = Test three, etc. In each column I would like only the test score listed (so not the test title - I will put this in the first row as the column header)
Is this possible?!
I have a column with random times that are not seperated by any commas or spaces how can i seperate them into different columns. For example in A1 i have 12:3213:5420:32 this represent three different times 12.32 13.54 and 20.32 (each time has the same format as shown) and i want to display these as B1=12:32 C1=13:54 and D1=20:32 then do the same for the rest of column A?
View 3 Replies View RelatedI have an interesting problem I've been presented with and rather than try formula after formula I thought I'd propose it to you all (see attached sheet).
I have 7269 rows with 1930 distinct claims. I need to pull out the Primary issue (done that) then across from it put in each and every secondary issue (from col C). The largest # of claim lines is 89 (see E1). So in theory I need to find that claim and put all the secondary issues in the next 89 columns from row 4990 beginning in col H.
I've done a couple examples of what I'm looking for in rows 2, 4 and 5 and 8 but beyond that ...
So every place where there is a value in col F I'd like the list of secondary issues in the same row.
As enclosed in my workbook,I want to separate the numbers between two strings of which one is in the left hand side and other is in mid.The data is in Column D.The simple way to understand is that,
Total Amount = Amount X Exchange Rate
Total Amount is in Column B, where as "Amount X Exchange Rate" is in combined form in column D.Yes, this I want to separate i.e Amount separately column and Exchange Rate in separate column.I have shown one expected result in column E,F.There are certain characters like ],= which you have to not to take any amount after ] or =.The currency is somewhere,$/$$/INR/IRS,etc.
I have a glossary with 400+ rows in column A. See examples below. I'd like to separate them so that the term is in column A and the definition is in column B. Once Done I will not need the . Normally instead of that tag I would have a hyphen separate the term from definition but the fact that some terms have a hyphen like "D-VHS" was throwing me further. In the end I will not need a separator because everything will be in separate cells.
DSL Digital Subscriber Line is a technology for bringing high-bandwidth information to homes and small businesses over ordinary copper telephone lines. A DSL line can carry both data and voice signals and the data part of the line is continuously connected.
D-VHS Connects a digital audio/video cable for use with some D-VHS digital VCRs.
E-Mail Messages sent to a customer's receiver that are viewed on their television screen. Pending messages are indicated by an icon on the on-screen status display (channel marker) and by having the Power button flash. (Not all receivers have a blinking light.)
I have a column within a spreadsheet that has data separated by 2 delimiters, a "" and a "/". (This data has been extracted from a linux-based file.)
For example: "1100789/3200899/6xlm-sgt-455-0987"
The items of data are from a bill of material (parts explosion) and the number preceding the "" is a quantity and the numbers preceding the "/" is a sub-part number of the main part number that is entered into a column to the left of this data string. (unseen in the example)
I need to take this string of numbers and place the quantity in one column, the part number in another column, then add a row and continue to populate each column until the data has all been separated, then move on to the next main part number row to continue the process.
for example:
1 100789
3 200899
6 xlm-sgt-455-0987
Is this possible with the data tools in a spreadsheet, or will I need to write a macro?
Whoever created my customer contacts Excel sheet prior to my arrival entered all of the contacts address information into one cell and in order for me to put this info into Access, I need them seperated. For example:
10000 X Street Louisville, KY 40291 is in cell E2
I would like it to read:
10000 X Street in cell E2
Louisville in cell F2
KY in cell G2
40291 in cell H2
They also did this with phone numbers (ie. desk# / cell# / fax#).
There are over 1000 contacts in this sheet, so it would take forever to split these apart row by row.
I have a spreadsheet which needs formatting I was wondering if anyone would be able to help with creating a macro to do so. The problem with raw report is that in cell A47 there are five columns worth of data in that one cell, then in B48 there are another two, in b49 and b50 one respectively. I manually format it by first joining the separate cells using
View 11 Replies View RelatedI have one column that has forename and surname information and I need to separate the surname into a separate column next to the forename.
Is there a formula for this?
I've tried the formula where you can request to move so many characters to the right, but it's difficult with all of the surnames being a different number of characters.
In Excel 2007, I would like to split Alpha Numeric Text to separate out Prefix portion of the text, example below represent Row no. 1, 2 , & 3
Original Text
Column A
Result A
[Code]....
In the same example, I would like to extract /Copy Result A or Result B into another cell.
I have 2 workbooks (workbook1 & workbook2). Information in column A and column N must match both workbooks per row/ cell (ie. if A2 and N2 in workbook1 match workbook2, copy information from 3 other columns (S, T, U from from workbook1 into workbook2 - cells S2, T2, U2). Workbook2 may have information in column S, T, U, but I need to over-write it with the information from workbook1. If the information does not match per that row/cell (ie. A2 in both worksheets are the same, but N2, they are not, so go to the next line that does match and copy the information into worksheet2). I do not want to use a V- lookup for this problem. I really need a marco.
View 3 Replies View RelatedI am trying to pull titles out of a text string but its quite difficult because of how the data was originally inputted. I need titles that appear in the following different formats
" title"
title/
title"
These titles were not inputted consistently and it could show up in the middle or at the beginning. I have attached a sample of the data that i am working with and a sample formula that i was trying to use to obtain my information. The formula that i have is a nested formula of find and mid formula that searches for the text within " ".
I am unsure if this was answered already but I am having a lot of trouble. I am trying to find out if it is possible to count the number of entries in one column if it meets criteria in another comlumn?
Here is my problem: I work for a contractor and I am trying to find out the number of contracts sold(column J) by Mike (Column K). I have tried =COUNTIFS functions but these dont work. I know if I create 4 other columns with IF functions I can get the information but I have to imagine there is another way.
I have two very long columns of client names. The columns represent reports that were run at different times on the same information.
What I want to do is merge the two clomuns. The issue is that there are many names that are the same but some that are different. Is there a way that you can merge the info. So it ignores the client names that are the same but auto adds the ones that are new. There are financials in other columns that I want to have come along during the merger.
I have the following code below, im not sure how to modify it to make it only search 2 columns of information, how can do this?
Private Sub CommandButton3_Click()
Dim StrFindWhat As Range
Dim NextCell As Range
Dim WhatToFind As Variant
WhatToFind = Application.InputBox("Please enter the Application or Service you want to search for?", "Search", , 500, 80, , , 2)
If WhatToFind "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[b4].Activate.....................
I have a list of names with blank cells in Column A cell 2.
I want the programme to scroll through this column until it finds a name, when it does I want it to look in cell offset(1, 1), in this case B3 to see if there is a number. I want this number to be moved to offset (0, 1) AND any subsequent numbers below it until the next blank cell in that column (B).
In the attached image cell B2 will contain all numbers in cells B3:B9 with a / between them. Cell B10 will have number from B11 and B12 will have B13:B15. If there is no number it will just continue to the next name in column A. My rough code below delivered the first number only in the relevant cell and I'm not clever enough to get it right. It does not have to be super slick as it's for one time use.
I need numbers in column B cleared as they are concatenated into the single cell.
Spreadsheet.jpg
Ok this is what I need to do:
I have worksheet A with cell AC2 to enter a team id# like 51922 for example. In Cell B3 I want it search sheet11 for the team name according to the number and put the team name in B3. Sheet11 has 11 columns the first one list the 613 team names, the remaining columns have #'s like the example giving for the teams. What formula would I need to perform this function?
This is a football game the other 10 columns represents 10 worlds with identical 613 teams to control in each world, but the teams have different #'s to identify the world, team, and coach.
I have a spreadsheet that contains overtime information for employees. Basically, it has a list of names in column A, and a list of projects in the first row, similar to this:
Name
100323
100887
100987
100987
Sum
[Code]....
At the top of the page I've inserted a section similar to this:
Name
Project Number
# Of Hours
SMITH, John
100883
5
Where the name and project number are drop down data validation lists containing all of the project numbers and employees at our facility, and the # of hours section is just a manual entry.
I require a macro that, upon clicking a button, will verify that the name is in the list and insert a row if there isn't as well as verify the project is in the row and insert a column it isn't.. then it will add the number of hours i specified in the box. It would also be nice if the list of names would sort alphabetically and the project numbers would sort numerically from left to right after any additions. As well as clear the selections and # of hours I entered in the second table shown above once everything has been entered in to the main (first) table.
I'm pretty new to creating my own macros (even though im an engineer.. )
I am having a problem filtering information within mulitple columns. I do not understand how the advanced search works either, so let me try to explain what is going on. I would post a pic of the table to make it easier to understand if I knew how to.I have thousands of rows in Column A with information, for this scenario a number. Thing is that all these numbers are seperated by many blank rows.
Ex: Row 1 has a number, Rows 2-10 are blank, Row 11 has a number, Rows 12-24 are blank, etc. Over in Column C, for every row that has a number in Column A, there is a corresponding number in Column C followed by Rows of numbers. ex: Row 1 Column A has corresponding information in Rows 1-10 of Column C.If this is clear, understand that this repeats for tens of thousands of lines. When I am looking for a specific number in Column A, I do a filter for just that number.
When that happens, it only brings up the info from that same row in Column C. I need the filter to bring up all ten rows of info in Column C that correlate to the row I filtered in Column A. I know the easiest way is to label all the rows in column A with the same number, but the reason the blanks are in place is so it is easier to read the breaks between the different information.
I have data that I copy from one sheet and move it to another. If I know I'm parsing data that may vary in amount of line items but will always be across 3 columns is there a way to parse this data evenly.
for example:
I would like to look in column "D" and if there is an "A" I would like to parse cells A,B,C in that row to sheet 2 and parse this information evenly across 3 columns evenly across columns "A,E, I"
In my example the data is only 21 rows of information the problem I have is evenly parsing this information.
I am trying to sort/filter the information by columns but I need the information in each row to stay together for instance...
1 2 3 4 5
2 3 4 5 1
3 4 5 1 2
4 5 1 2 3
5 1 2 3 4
I need to sort the numbers in column 3 in ascending order or filter them but i need the entire row to move when sorted so it needs to look like this
4 5 1 2 3
5 1 2 3 4
1 2 3 4 5
2 3 4 5 1
3 4 5 1 2
I am using the list function to sort and filter the data in the columns but when i try to sort them in ascending order i get....
1 2 1 4 5
2 3 2 5 1
3 4 3 1 2
4 5 4 2 3
5 1 5 3 4
I don't know how to write formulas or VBA's or anything but am willing to try.
Is it possible to sort the data and get a result like the second set of data? and is there an easy way to make this fool proof so people that use the spreadsheet will not mess up the data?
I have a 5 row list in WA2. Each row holds its data across 3 columns i.e A1description, B1 product code, C1 unit cost. In WA1 I want to have a drop down table in cell B3 that looks at this range and then populates this information into B3, C3 and C4. I have tried data validation but it keeps telling me that it can only look at a single row or single column
View 2 Replies View RelatedIs it possible to automatically have columns repeat through all sheets in a workbook? I keep an annual book, broken down by sheets for each month. I always have to add 5 columns to each sheet, and was wondering if there was an easier way..?
View 3 Replies View RelatedI have a report that the name and address was put into one cell. I need to put this into numerous columns so I can do a mail merge.
View 9 Replies View Related