Separating Data With Spaces And Putting In Two Different Cells

Aug 18, 2009

I need to take data from a cell that has a space "cherry banana", and make it into two cells "cherry" and "banana". I want to duplicate my line on which the data resides, if possible.

What I have is a sheet like this:

View 5 Replies


ADVERTISEMENT

Advanced Concatenate With Spaces Separating Numbers

Jan 13, 2009

I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).

Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this :

=CONCATENATE(B4:B127)

How do I add spaces to a range?

My problem is very similar to the question asked in this thread [URL] .... but I am using numbers and would like to separate them with spaces.

View 9 Replies View Related

Advanced Concatenate With Spaces Separating Numbers

Jan 13, 2009

I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).
Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this =CONCATENATE(B4:B127), how do I add spaces to a range?

my problem is very similar to the question asked in this thread http://www.excelforum.com/excel-work...ncatenate.html
but I am using numbers and would like to separate them with spaces.

View 6 Replies View Related

Putting Data From Several Cells In One Cell Together

Oct 10, 2011

I have a sheet with 2 colomns: client number and document number, as follows:

Client number Document number
1234 100012
1234 100042
4321 102233
4321 249123
4321 533213
4567 134123
7890 123451
7890 125675
7890 357353

I need to have a unique client numnber on 1 row with all his documents in one cell, as follows:

client number: document number

1234 100012, 100042
4321 102233, 249123, 533213
4567 134123
7890 123451, 125675, 357353

View 5 Replies View Related

Separating Data From One Cell To Multiple Cells In Same Row?

Mar 3, 2013

I attached sample file with data I received (more than 50k rows in each file) is contained in 86 files with varying population lengths in each file that needs to be broken down as shown in the attached file.

View 5 Replies View Related

Subtracting Two Arrays Of Data And Putting The Result In Some Cells

Dec 11, 2009

I was trying to subtract two arrays of data and putting the result in some cells but with no success unfortunately. I'm relatively new to VBA and I'm just starting now to make calculations with arrays so excuse my little knowledge. The arrays that I'm trying to subtract are from row 1 to 250 and m and n variables have the number of the columns. Here is my routine:

View 3 Replies View Related

Putting Two Cells Together With 0

Mar 26, 2014

I have a cell in A1=(str identification is = ",) and in B1=(2). In C1 I want it to equal (str identification is = ",02) In C1 I have A1&B1

View 4 Replies View Related

Separating Information In Cells

Aug 6, 2007

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:

View 9 Replies View Related

Separating Cells In A Worksheet

Jan 2, 2009

Set up a worksheet so that it has 2 separate sections in the same worksheet ????? I am wanting to have 2 different sets of information in the same worksheet - and need the columns to be different sizes based on the information i want to put in. Is there any way to have this done - as I know that if you change the column width it does all columns - and i only want to do select columns. For example for section 1 - i would like to have the column widths as follows:

A1 = 20
B1 = 40
C1 = 10
D1 = 20
E1 = 40
A3 = 20
B3 = 40
C3 = 10
D3 = 20
E3 = 40

This is where i want to put details such as names, dates and results.

Then for section 2 - i would like to have the column width as follows:

A6 = 10
B6 = 45
C6 = 15
D6 = 30

This is where i want to have a set of questions to answer.

View 4 Replies View Related

Putting Borders To Specific Cells

May 31, 2012

In column A of my worksheet I have values starting from A4 as below:

A4-1000
A5-1000
A6-1000
A7-2000
A8-2000
A9-1500
A10-2200
A11-2200
A12-2200
A13-2200
and so on till A10200.

What is want is to put thick box borders for similar values using a macro. For e.g. i want to put the borders to first 3 cells together as these are of same value 1000, then it has to be put to A7 & A8 (Value - 2000), then for A9 alone, then for A10 to A13 together till i have values in column A.

View 5 Replies View Related

Separating Words In One Cell Into Many Cells

Jun 12, 2014

All words are in Cell A, separated by the ";" symbol. I want separate into different Cells. Example... Lead_ID in Cell A1, Application Data in A2, Date Purchased in Cell A3 and so on.

lead_id;application_date;date_purchased;first_name;last_name;email;day_phone;work_phone;
best_time;city;state;zip;addr;cred_rating;cred_score;yearly_income;prop_state;prop_type;purpose;
loan_amount;prop_value;ltv;mtg_bal1;mtg_bal2;services;credit_repair;foreclosure;debt_management;comments

View 5 Replies View Related

Auto Separating Groups Of Like Cells

Oct 8, 2008

I am working on this project for work that is pretty large (about 13500 rows) that is filled with items that I currently have sorted alphebetically. What I need to do is if there is a group of cells that have the same code, I need to insert a row at the very end of that group and autosum the quantity. It is becoming very time consuming doing it all by hand so I am trying to find out: is there a function that will automatically insert a row after each group of codes?

View 2 Replies View Related

Separating Info From One Cell Into 2 Different Cells

Aug 28, 2007

Whenever I get information from finance.yahoo.com or from my job and put it into excel all the information seems to go in one cell and numerous rows. (I think its b/c I am putting information that isnt' meant to be in excel and forcing it to open up there.)

For Example in cell A1 I will have
"AIG , AMER INTL GROUP I , 66.08 , 1:16pm , 169.455B , 16.736 , 2.01 , 9.47 , 0.78"

Cell A2 I will have
GOOG , GOOGLE , 508.9 , 1:21pm , 158.845B , 22.361 , 17.511 , 26.29 , 1

What I want to see is cell A1 as AIG and cell B1 as 66.08 and cell A2 - GOOG and cell B2 508.9. I don't even want the rest of the stuff.

Is there any way or formula I can use to just pull the information out of cell and have it separated for me?

View 11 Replies View Related

VBA - Putting A Border Around Cells That Arent Empty

Nov 3, 2008

I am trying to Put a border around cells that arent empty. Is this possible?

View 11 Replies View Related

Separating Text In One Cell Into Individual Cells

Jan 9, 2009

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 Related

Putting Specific Value In Blank Cells In Same Row Based On Value In 1st Cell?

Jul 18, 2013

i am trying to Put a specific value i,e "NO Deliveries" in blank Cells in the same row based on value in 1st cell of the Row which has a fixed value . note:the columns and rows are dynamic for example: I want to put the value "no Deliveries" in cell C because its blank.

A B C D

% of Availability
0.00%

80.00%

View 9 Replies View Related

Select Certain Cells Based Upon Condition And Putting In Sheet 2

Mar 25, 2009

I have attached the file with this post.

What I like a macro to do is:

Whenever a 'D' ends in column "Step 5" the last value of 4 cells to the left to be put in sheet 2 in reverse order.

View 5 Replies View Related

Putting Together The Data From 2 Sheets?

Apr 1, 2014

There are two sheets with data:

Sheet 1
Column A - reservation number (unique)
Column B - trade partner

Sheet 2
Column A - reservation number (unique)
Column B - Hotel property

I want to put in one sheet: Reservation number - trade partner - hotel property

In another words I want to have a list of all reservations according to hotels and partners.

View 2 Replies View Related

Separating Data In A Cell?

Aug 20, 2014

I've been tasked with separating data within an Excel cell into it's own row. The difficulty I am having is, I need to keep the ticket number each piece of data within that cell is linked to. For example, I have Column A that has the ticket number, and column I has the serial numbers of each piece of equipment linked to that ticket number. I would like the serial numbers separated into their own row, but still keep the ticket number in which that equipment is connected to.

Here's an example: Column A Column I
Ticket # Serial Numbers on Account
23999866 PAFR06598067 - MOPCUDTAM

Would I need a formula to do this, or is this something I can do using one of the Ribbon commands?

View 9 Replies View Related

Separating Or Sorting Data

Sep 22, 2008

for example:

Column A.

@yahoo.com
@hotmail.com
@live.com
@msn.com
@yahoo.com
@hotmail.com
@live.com
@msn.com

how can i separate all datas by domain without having to cut and paste them manually.

View 7 Replies View Related

Separating Data From Days?

Jul 29, 2012

What I am looking for is a macro that can separate the data introduced manually in the left table (see attach file) and order it in the table from the right hand side as I have done it manually in this case. The base of it is to have at the end all the trips separated from days (days going from 0:00 hrs till 24:00 hrs). Maybe is a bit confusing but in the example I have attached I presume is going to be easier.

View 3 Replies View Related

Putting Lines Of Data Into A Table

Dec 23, 2013

I am having trouble being able to put data into a table. The data I have breaks down orders of products in crates into lots of 45 crates with a remainder.

I would like to be able to fill a table from left to right which displays the item number with the corresponding amount of crates, which seamlessly goes from one item to the next.

I have attached an example.

pallet data.xlsx‎

View 5 Replies View Related

Separating One Column Of Data Into 3 Based On Its Value?

Jan 16, 2014

I am trying to divide one column of data into three. I can't change the order of the data because it is set up as a schedule at my work.

Here's how the info is sent to me, lets say columnA Row2:

500001
500003
500002
700002
700003
700001
800003
800001
800002

Here's what I need my spread sheet to do.....lets say in column's C, D, and E (all in Row 2)
C D E
500001 700002 800003
500003 700003 800001
500002 700001 800002

View 6 Replies View Related

Separating Different Data That Resides In 1 Column

Sep 8, 2009

I have several large excel spreadsheets with different pieces of information that appear in a single column and I am looking for a way to seperate the data into multiple columns. I have read many different articles and how-tos and none seem to help with what I have...here goes:

I have a column that has a few thousand lines of info that looks like this:

Acme Stores
Name:
Smith, Steve
Reed, Tom
ShopRite Stores
Name:
Stadler, Fred
O'Neil, Tim

etc.

So, its a repetition of the store name, then a cell that just says 'name', followed by the people names associated with that store.

I am looking to somehow separate out the store names, leaving just the people's names in that column - but keeping the store names in a seperate column so the people names can be associated with the store name. The only thing that I think might be able to help, is that the 'name' field is constant and its always the cell below the store name. I don't know if that is something that is even remotely helpful here, but thats the only thing I can think of if someone knew some type of conditional command that would pick out a previous cell from one with the word 'name' in it.

View 9 Replies View Related

Stacking Columns - Putting Data In Right Order

Mar 7, 2012

I have a question about putting data in the right order. I have this order in my excel files:

City A, City B, City C
10 20 20
20 40 50
15 30 40
45 20 10
50 10 10
20 30 40

I need to put them in order from top to bottom like this:

10
20
15
45
50
20
20
40
30
20
10
30
20
50
40
10
10
40

Is there any way to do this in a fast way. Because I have 440 cities and 13 numbers per city copying and pasting will take a long time..

View 1 Replies View Related

Separating Data From One Column Into 2 Separate Columns

Apr 10, 2013

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.)

View 4 Replies View Related

Putting Data Collected In UserForm Back Into Worksheet

Apr 14, 2013

I'm trying to set up a relatively simple UserForm, which will collect the name of a project from a ListBox, a project update which will simply be text entered into a TextBox, and a estimated completion percentage (factor of 10, 10/20/30% and so on) which will come from another ListBox.

I've managed to get many of the basics working, for example an OK button and a CANCEL button, but there are a few bits that I'm still finding rather tricky.

I have the selected date in the percentage listbox going back into my chosen place in the spreadsheet quite happily, but I can't get it to work for the project name listbox (I think it's because the options come from rowsource and weren't entered manually like the other one). I'll paste my code at the end so you can all see how I've done it (and have a laugh)!

I have also just about managed to get the data to go to the right place in the spreadsheet. I want the data to appear in columns, column 1 for the project name, project update in 2 and percentage complete in 3. I have this working, but it for any reason one cell becomes blank, my code uses that cell, and that could cause entries to become mixed up. I want the UserForm to enter data on the first entirely empty row.

Here's my code:

VB:
Private Sub Cancel_Click()
Unload ProjectUpdate
End Sub

Private Sub Clear_Click()
Call UserForm_Initialize
End Sub

[Code] .....

View 7 Replies View Related

Separating Data With Multiple Delimiters Into Columns AND Rows

Apr 8, 2013

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?

View 9 Replies View Related

Separating Data Entries Of Different Lengths All In Single Column

Jan 20, 2008

a way to separate out data that has been all entered into a single column

code .....

The above information is a sample of the data. The data has been entered into 1 column, column A.

I want to separate the entries in to 1 column each for vehicle ID, full journal description, actual journal, EE code, Report total and equipment description.

View 9 Replies View Related

Getting Rid Of Spaces In Text Within Cells

Feb 14, 2014

I have a large spreadsheet that I need to take the spaces out within the text in each cell. Is there an excel function or macro that can do this? It would save me lots of time rather then having to go in manually and doing it!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved