Parsing Data Strings Into Columns

May 4, 2009

I have a spreadsheet that gathers information from multiple cells and strings the data into a long string - each piece of data broken by a comma in the string.

In another spreadsheet, I copy this data string into column A on sheet 1 - starting on row 2 and down.

On sheet 2 - and this is where I need help - I would like to have a macro that will take each data string on Sheet 1 column A and parse it out into multiple columns but the same row on Sheet 2.

ColumnA Row2 Sheet1 -> changing to many columns on Sheet2 but still on the same row.

The data string always represents the same number of data components - its just a matter of breaking it out and placing each data piece into its own column.

View 11 Replies


ADVERTISEMENT

Parsing Strings In VBA

Apr 3, 2007

I am trying to parse employee names into seperate string variables in order to display surname, first name and middle name in seperate fields on a form.

The format of the full name is "Surname, First Name, Middle Name (If any)

I have managed to deal with surname and firstname but I am stumped on middle name. Not all employees will have a middle name but for those who do, I need to parse it.

View 9 Replies View Related

Maco Functions For Parsing Sub-Strings

Jun 27, 2008

in vba. is left and right the only way to extract substring?

e.g.
œ AAA (BBB 14:45:8)
i only need AAA and the numbers in 2 columns

View 7 Replies View Related

String Parsing With Variable Length Strings

Mar 28, 2006

I am trying to split up a cell into numbers and charachters and place them in
separate columns, but the lenght of the number part varies as does the
content of the character part.

For example, one cell could be 5#, 10Tins, 4 lb, 100Pcs, etc.
I would like to be able to pull out the #, Tins, lb, and Pcs in to their
own column.

I have found this formula:
=LEFT(A1,FIND("-",A1,1)-1)

but it assumes some level of consistency, the "-" in the cell.

View 18 Replies View Related

Excel - Parsing Numeric Data From Table Into Separate Columns

Nov 18, 2013

100% Bran Nabisco 70 130 10 5 6

AlI-Bran Kellogg 70 260 9 7 5

All-Bran w/Extra Fiber Kellogg 50 140 14 8 0

I am having trouble parsing the numeric data from the following table into separate columns. The five different numbers (ex. 70, 130,10,5,6 in the first row) represent nutritional info about the product. As you can see, the number length varies for the different categories. How to get these 5 numbers into their own columns using LEFT, RIGHT, MID, FIND, etc. functions.

View 3 Replies View Related

Parsing And Appending Contents Of Filename Into Columns?

Oct 21, 2013

I have a large number of Excel files with unique filenames that contain information I would like to add as columns to the actual spread sheet.

SPY_P_20131025_172.00_XX

I am trying to in break apart the above format with each of the above underscore separated fields being placed in there own column. How I would accomplish something like this?

...And if I wanted to perform this function on a large batch of files is there a tool I would need to automate the process?

View 6 Replies View Related

Extracting Strings Of Text From Columns Of Data(text)

Apr 23, 2007

What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells

The data I require is :-

(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson

(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)

(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting

See examples 1 & 2 below

Example 1

CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting

Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before

Example 2

CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers

Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.

As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text

View 9 Replies View Related

Parsing Data

Jan 8, 2007

I have a parts list which came from one source in the format below. First column is the name of a product, the following columns show what components and what quantity go into the product:

Small Fruit Basket Apples 2 Oranges 3 Bananas 4
Large Fruit Basket Pears 5 Peaches 6 Mangos 2

The format I need it to be in is below, but I don't know how to go about it or if it's even possible:

Small Fruit Basket Apples 2
Small Fruit Basket Oranges 3
Small Fruit Basket Bananas 4
Large Fruit Basket Pears 5
Large Fruit Basket Peaches 6
Large Fruit Basket Mangos 2

View 9 Replies View Related

Parsing The Data

Aug 5, 2009

I have arrived at the following in order to find the 5th "bar" in the data (samples below) :-


=SEARCH("|",$M127,SEARCH("|",$M127,SEARCH("|",$M127,SEARCH("|",$M127,SEARCH("|",$M127,1)+1)+1)+1)+1)
Samples:-
STR|6|L||396|3| STR|4|L|114|396|3| NHF||||1463|175| RND|4|H|111|2352|49|

Is it possible to reduce the repetition within the formula?

View 9 Replies View Related

Parsing Data In A Cell

Jan 24, 2010

I'm looking to set a limit to the parse though.

For example:
Data in a cell:
Something x2345 is good
Anyone watching x56784 Yay! 345!

using:

View 2 Replies View Related

Parsing Pasted Data

Oct 13, 2008

macro that will parse data that has been pasted in to Excel from a text document. Once the macro is executed the pasted data will be sent to a separate spreadsheet and parsed horizontally with the data residing in is respective cell.

Here is an example of my data from text:
FIRST: Frank
MIDDLE: N.
LAST: Stien
ADDRESS: APT14
STREET: Halloween
ZIP CODE: 123456
CITY: Scaryville
STATE: Scary Ya
COUNTRY: Transilvania
COMMENT: N/A

Here is an example of my desired results:
A2:Frank, B2:N, C2:Stien, D2:APT14, E2:Halloween, F2:123456, G2:Scaryville, H2:ScaryYa, I2: Transilvania, J2:N/A

The data is always pasted in A2 on the first sheet, and varies somewhat in length but the format is always the same.

View 5 Replies View Related

Parsing Address Data

Oct 9, 2009

I have been given a set of data from one of our clients with company and address information in a Microsoft Word document. The data is not formatted into fields delimited by either a comma or tab and none of the fields has a heading of any sort. The data flows vertically rather than horizontally. I have imported the data into Excel in the hopes of being able to break out the data into an Excel or csv format in which the data can be imported into a Filemaker Pro database. Its format is the following:

Company Name (cell A:1)
Address (cell A:2)
City (cell A:3)
State (cell A:4)
Zip (cell A:5)
telephone (cell A:6)

I have tried recording a macro that moves the data from cell A:1 into cell B:1, then cell A:7 into cell B:2, etc. But have failed to get a macro to work that runs through all of the data in column A:A. The data is consistent - that is, there are only the 6 rows of data. Is it possible to write a code that moves the data from cell A:1 (the company name) into cell B:1, then skips 6 rows to cell A:7 (the next instance of a company name) and moves cell A:7 into cell B:2. The same procedure would then be run on the address, city, zip, and telephone.

View 2 Replies View Related

Parsing Data - Get Any Number That Is Before C And C Itself

Mar 21, 2012

Here is an example of some data I need to parse.

IN: CS, Test 2A-1, CBL, PHL, South Penn I/L, Loc P10, 49C-Loc P11B, AP 1.5

That code that I have so far:

select t.description,
REGEXP_SUBSTR(t.DESCRIPTION, '[^,]+', 1, 1) col_1,
REGEXP_SUBSTR(t.DESCRIPTION, '[^,]+', 1, 2) col_2,
REGEXP_SUBSTR(t.DESCRIPTION, '[^,]+', 1, 3) col_3,

[Code] .......

I have 2 problems:

1st problem is that for COL 7, all i want in there is the "49C", I'm currently getting " 49C-Loc P11B".

My ultimate goal is to get any number that is before the C and the C itself. The number before the C could be 1 to 3 digits.

2nd problem is Column 9. That snipit of data will always start with an "A", have another alpha, then a space, then a number. I'm trying to just get the number.

View 2 Replies View Related

Parsing A Vba's Variables Data

Jun 13, 2008

one="dog"
two="cat"
three="bug"
four="bird"

I have a 5th variable with a sentence in it for example

Sen=”The bug and bird are in the park”

What I need to know is which of the variables content appears in the variable Sen first and what position it is. In this case it would be variable three position 5.

View 9 Replies View Related

Parsing Data Using $ Symbol

Mar 13, 2009

In general it looks like this: All in one column

flight 1.1
flight 1.2 paid $50 owes $100
flight 1.5 paid $80
pass paid $125
ground 1.2 flight .9
checkout flight 1.2 paid $76

I need to extract the numbers with a $ sign into a separate column. to look like this:

flight 1.1
flight 1.2 paid $50 owes $100...........50..........100
flight 1.5 paid $80...........................80
pass paid $125...............................125
ground 1.2 flight .9
checkout flight 1.2 paid $76...............76

.............. representing separate columns

View 9 Replies View Related

VBA For Parsing And Exporting Data From Worksheet

Jan 21, 2013

a VBA code for PARSING and EXPORTING data to multiple excel workbooks based upon unique entries in column 'N" only for those rows for which column Y value is "ACTIVE".

I am currently using a code which allows me to export data based upon unique values of column N but how to weave in an additional pre-filter criteria of Column Y value being "ACTIVE".

View 5 Replies View Related

Parsing/renaming Data In A Cell

Dec 12, 2009

Sheet4

ABCDE1Source DataDesired Result
Include these extensions: Exclude these extensions:2OLD.FILENAME.rar.htmNEW.FILENAME.rar
.part*.rar.htm3OLD.FILENAME.part01.rarNEW.FILENAME.part01.rar
.rar.html4OLD.FILENAME.part02.rar.html.htmNEW.FILENAME.part02.rar
.avi
5OLD.FILENAME.txtNEW.FILENAME.txt
.zip
6OLD.FILENAME.zip.htmNEW.FILENAME.zip
.txt
7OLD.FILENAME.aviNEW.FILENAME.avi
.7z.*
8OLD.FILENAME.7z.01.htmNEW.FILENAME.7z.01

9OLD.FILENAME.7z.002NEW.FILENAME.7z.002

I'm looking for a formula which can take my original data (column A) and "transform" it into what you see in column B.

I am transferring large amounts of files from various servers to my ftp and back out again. My original data are url filenames which of course, are named differently and not how you see it in my example.

The filenames themselves are preceeded by website names and varoius folder/path names. I edit the links to include various Usernames and Passwords and at the same time, rename the original filenames. Then I transfer the files to my ftp.

Most of the files have a ".part*.rar" extension at the end, so my formula has only included this scenario. Rather than manually editing my formula everytime a different extension is present (about 20-30% of the time), I would like to find a way to automatically look at a table/list of possible extensions (D2:D7) which are to be recognized for the sake of finding the end of the filename itself, so that I can rename (SUBSTITUTE) the original filename to that of my choice (cell D13) and of course, also include the original extension(s) of the source data (keeping this is important, especially in the cases where ".part*.rar's" are present). The list of extensions in cells E2:E7 are to be excluded from the new filename.

My present formula works fine for the editing of the username and passwords for various servers and for finding the beginning of the filename in the original url. It also works fine for finding the end of the filename (for the sake of renaming it), but only in cases where a .part*.rar" is present.

View 9 Replies View Related

Counting Strings Across Columns

Jan 9, 2014

I need to get the count of a string from multiple columns. The problem is that string1 exists in column A and String2 can exist in one of eight columns. See the example below.

What I want to do is use two search criteria. I want to find the number of times I have Type=A and if the row has a case Beta. In the example above I should get an answer of 2. (From rows 3 and 9)

I tried countifs but I cant seem to get it working properly.

Attachment 288701

View 3 Replies View Related

Pulling Out 1/2 Char Strings Into Other Columns

Mar 2, 2009

I'm new to this, and trying to get a spreadsheet organised for my nba fantasy team. To save time, (and me having to enter the positions of 300ish people individually) I'm trying to pull out their positions into seperate colums.

The data I am trying to pull is from these:

Jarrett Jack, Ind PG
Gerald Wallace, Cha SF, PF

=IF(FIND("PG",$B2),"PG","")

That is the formula I am trying to use, it finds the PG for some, but when it doesn't it gives me this "#VALUE!" in the cells, instead of a blank as I have tried to input.

Now, there are 5 combinations that I am trying to pull out: PG, SG, SF, PF and C.

There are multiple combinations that need to be pulled out, and placed into my 3 cells allocated (EG - 1 for Pg, 1 for SG, 1 for SF).

I have also found a flaw that takes the C from Cha (being the team) instead of from the position. Can I search for a individual C? I've tried to google these things, but I can't find an answer.

View 11 Replies View Related

Split Alphanumeric Strings Into Three Different Columns

Feb 27, 2014

I am working on a report but I need to split of text cells that contain text and numbers. I had worked in some formulas and I got stuck again when I drag down the formulas. In the attached file, you will see two tables. The one in the left is my current job. In the table of the right is how the table must look like.

The table is complete and will give you the whole idea.

Basically, I need to split the cell into three categories that are "Family", "Model" and "Phase". In example:

Cell Family Model Phase
CCA CCA
DC50X DC50X
DCX3300CRDDCX3300CRD
DPC2434 DPC2434
DCT-1700DCT1700
DCT-1800DCT1800
DCT-1800P3DCT1800P3
DCT-1800P4DCT1800P4

Attached File : Family Model.xlsx‎

View 4 Replies View Related

Delete Rows With Strings In Only One Of Two Columns

May 10, 2012

I have two columns. In the first column, there is in every cell some text. In the second column, there are some blank cells. It looks like this

textCOL1 textCOL2
text1COL1
text2COL1 text2COL2
text3COL1 text3COL2
text4COL1
text5COL1
text6COL1 text6COL2

How do I remove the rows which have text only in the first column?

The output should be:

textCOL1 textCOL2
text2COL1 text2COL2
text3COL1 text3COL2
text6COL1 text6COL2

View 4 Replies View Related

Excel 2010 :: Parsing Data From Text File To Create Scatter Plot?

Jan 11, 2014

I have a text file which is attached as "rawdata". It contains records of something (let's call it temperature) at different times on different days. My goal is to display a graph of temperature versus time so that I can visually analyze trends. I have hundreds of these files, all of different lengths. it is very important that I automate this process as much as possible.

Detail: (Here I describe what I have done so far; if this is inefficient or unnecessary, feel free to tell me) I open Excel 2010, click File, Open, and select the file that I want to parse. It is a TXT file, so the Text Import Wizard comes up. For step one, I select Fixed Width. I select File Origin: MS-DOS (PC-8). On step 2 of the wizard, I create column break lines to place all dates in the far left column. The next column contains the first column of numbers before the first dash (-). The next column contains only the dash - I will later select "ignore this column" to eliminate them. The next column contains the time stamps. I continue adding column breaks in the wizard until all of the data are parsed into columns in the same manner.

In step 3, I format the first column as "date (DMY)". The columns with the dashes I select "do not import". Everything else is "general". I click "finish", and the resultant workbook is attached, called "import".

Now, as to what I want to do: I want to display the "temperatures" as a graph vs a date/time axis. The reason I find this difficult is because the temperatures and times are not in neat columns, but are in 4 columns that go in a left-to-right and top-to-bottom progression and are broken up every few lines. (I am interested only in numbers that are displayed immediately to the left of a time-stamp. Therefore, the "record #"s should be ignored. We can delete the rows that say "record #" if can be done automatically.)

View 3 Replies View Related

Separating Or Extracting Numbers Between Two Strings Into Two Columns

Mar 13, 2014

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.

View 4 Replies View Related

Text To Columns - Separating Numbers From Character-strings

Feb 10, 2014

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

View 5 Replies View Related

Finding Strings In VLookup Value - Embedded In Larger Strings Within Table Array

Dec 20, 2012

I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array

Essentially I imagine this involves the FIND function at some point.

Attached is an incredibly simplified example of what I'm looking for.

View 9 Replies View Related

Search For Parts Of Strings In Another List Of Strings

Dec 16, 2013

I have an excel worksheet with about 10K rows of data in column A.

I have also another list of data, about 200 rows of data, in column G.

I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.

Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com

in column G:
row 1: help
row 2: info
row 3: support

I need rows 1, 3 and 4 in column A to be colored.

View 9 Replies View Related

Extracting Data Between Two Strings?

Feb 19, 2014

how I can extract data between two strings?

Example: My string is: -_creativename-_spongecell-_creativesize-_300x250-_creativetype-_spongecell-_

How can I create a column that contains everything AFTER 'creativename-_' and before '-_creativesize-_'?

View 11 Replies View Related

Compare Text Strings In Two Columns And Return Text From Adjacent Cell

Feb 28, 2013

how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?

View 6 Replies View Related

Transposing Data & Tokenizing Strings

Aug 18, 2009

I have some data in the following format:

Spreadsheet #1
Code Country Digit
0582 ALGERIA 21312 Earth
0582 ALGERIA 21313 Earth
0582 ALGERIA 21315 Earth
0582 ALGERIA 21316 Earth

I have found the following example in the forums that has helped me get to the following:

Code Country Digit
0582 ALGERIA 21312 Earth, 21313 Earth, 21315 Earth, 21316 Earth

Sub a()
'Written by Barrie Davidson
Dim DataRange As Range
Dim DataSheet As String, SummarySheet As String
Dim Digits As String

Application.ScreenUpdating = False
Set DataRange = Range("A1:C" & Range("A65536").End(xlUp).Row)
DataSheet = ActiveSheet.Name
Sheets.Add before:=Sheets(DataSheet)
SummarySheet = ActiveSheet.Name

But I need it to be in the format:

0582 ALGERIA 21312 , 21313 , 21315 , 21316, Earth

I have tried working it out myself but am a macro/excel novice (and have gone cross-eyed).

There are over 10000 lines of data so unfortunately I cant just do it manually.

View 9 Replies View Related

Change Strings Data Alphanumeric

Jan 25, 2010

I have in two cells :
a1 b1
1m 1-3-6-11-17

how can I get:

1m1
1m3
1m6
1m11
1m17

using vba code.

View 8 Replies View Related







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