I have the attached spreadsheet that has 176K of addresses. The cell contains data that is actually Address Line 1 and Address Line 2. I need to separate these. I believe there is an unprintable character that separates most of them -- maybe a CRLF or something like that.
See the attachment for examples ... Cust Address with unprintable characters.xlsx
I have some code that will parse the FIRST IP address it finds in a string. Could someone help with the code for continuing through the remaining part of the string and parsing out any additional IP addresses it finds and concatenating them with a comma and space (", ")?
There are specific rules for validating a "proper" IP, but for the purposes of my work, I don't need to validate. I found the following code on a google group (thanks Jeff M). All it does is parse the numeric text surrounding the 3 periods that all valid IPv4 addresses contain. For my purposes, it works ....
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:
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.
I have an email sent via pdf and the only way to get this into excel is by copying and pasting and it all shows up in one cell.
Not much to focus on other than reading from left to right, once you see a numeric value, this should be in another cell and once you see "LS or WS or NR or COA, this will begin another cell. I had to add the dots to show where the new cell starts.
parsed data
SHELL TBN...130 CURACAO/JAPAN 18JUL... LS 3.675M PDVSA SUBS
EVERGLADES...70 CHIRIQUI GRANDE/USG14JUL... NR BP
TEXAS STAR...70 ECMEXICO/USG 11JUL... NR NR
MINERVA LIBRA...70 BAHAMAS/USG 11JUL... WS 80 STATOIL RPTD
original data
SHELL TBN 130 CURACAO/JAPAN 18JUL LS 3.675M PDVSA SUBS
EVERGLADES 70 CHIRIQUI GRANDE/USG14JUL NR BP
TEXAS STAR 70 ECMEXICO/USG 11JUL NR NR
MINERVA LIBRA 70 BAHAMAS/USG 11JUL WS 80 STATOIL RPTD
CPO ENGLAND 35 ECCANADA/OPTS 07JUL NR CREST ENERGY FLD
Parsing values in a column. COLUMN A contains comma separated numbers, varying from possibly an empty cell, to a list of values between 1 and 12 separated by commas. I need to examine the column and place the number 1 in the column corresponding to the number. There will be no duplicates in COLUMN A. Is there an 'easy' way to do this. The table is part of a much larger spreadsheet.
COLUMN A
Team 1
Team 2
Team 3
Team 4
Team 5
Team 6
Team 7
Team 8
[code].....
i have a 2D array of numbers (7 columns x 30 rows which doesn't change size nor place in the book).
I have the address of the "starting" and "ending" cells of the area I need to sum (often in more than one row) squeezed between other tables which must not be part of the calculation.
How can i get the sum i need without macros?
Ex:
||A|B|C|D|
|1|1 7 5 0|
|2|5 6 8 4|
|3|2 6 7 4|
|4|8 4 5 3|
Sum from B2 to C4 = (6,8,4,2,6,7,4,8,4,5) = 54
I have a list of email addresses. They are in column A.
The 1st email address is in cell A5 and goes down until a blank cell is reached.
QUESTION: Is there a limit on how many email addresses can be contantinated together ? I can limit the number if necessary.
The contantinated list should be placed into cell A1.
I've got a list of over 170 names in excel rows that I need to quickly turn into a copy and paste list that I can mass email some of my sales people.
Example:
Amyp@emailaddress.com
Treverp@emailaddress.com
I need to combine the names and separate by a semi colon: Amyp@emailaddress.com; Treverp@emailaddress.com
I started a concatenate formula, but because each name is on different row, it was a lot of manual formula tweaking.
I have Excel WB / Sheet with filled columns A (serial No.), B (name), C (e-mail adress), D (problem), E (solved / unsolved) and now I want that Excel automaticly sends e-mail notification to specific e-mail adress (column C) when its / his problem is solved (column E).
View 2 Replies View RelatedI have two sheets: sheet1 and sheet2.
In sheet2 I have a column "C" called addresses and in that column I have actual cell addresses such as $J$740, $H$756, etc., all referring to cell locations in Sheet1.
In sheet1 in column "B" are names.
I would like to be able to return the names from sheet1 column "B" to column "G" of sheet2 that belong to the cell address from column "C" in sheet2.
Is there an Excel formula or vb script that will do this?
I tried "=INDIRECT("sheet1!"&C2)" but all that did was return was is written in the cell address (for example $J$740) but what I want is the corresponding name in column B.
= IF(AND(MATCH($B8, [AXS.xls]Sheet1!$E$1:$E$400,0) - 2 = $C8,
INDEX([AXS.xls]Sheet1!$B$1:$B$400, MATCH(CX$2, INDEX([AXS.xls]Sheet1!$B:$B, $C8 + 16):INDEX([AXS.xls]Sheet1!$B:$B, $C9), 0) ) ),
INDEX([AXS.xls]Sheet1!$A$1:$E$400, MATCH(CX$2, INDEX([AXS.xls]Sheet1!$B:$B, $C8 + 16):INDEX([AXS.xls]Sheet1!$B:$B, $C9), 0), 3), " ")
i have this formula, and i think it will work if i find a way to correct the blue areas. they are trying to define the match array. i tried using the address and cell functions with no luck. i just want the array to return something like B2:B15 (from the AXS sheet) so the match function works. in the current sheet, C8 and C9 have values that refer to rows in the AXS sheet
I need to return a value from a cell in Column H, but with a row number that varies with each entry.
I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:
=Display Value from in column H: (row number per cell B1)
And cell B1 would include the number 3789.
So the returned result is the value from cell H:3789
I have the following code always throwing this error message: "subscript out of range". When i run the macro with worksheets that only have 10 rows each, it's running fine. I suspect some issues on the line: a = Range("a1"). CurrentRegion.Resize(, 3) - the debug always throws the error before entering the loop. This macro is supposed to find the lowest price among 3 spreadsheets by matching the "Code" column and sending the final result into a new spreadsheet. But since im trying to run the macro on spreadsheets holding over 6000 lines each, this error is appearing.
Sub kTest_v2()
Dim a, w(), i As Long, z, dic As Object
Dim MySheets, ws As Worksheet, MinPrice
MySheets = Array("Sheet1", "Sheet2", "Sheet3") 'change to suit
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
For Each ws In Sheets(MySheets)
With ws
.Activate
'assume Col A Country, Col B Code and Col C Price
a = Range("a1").CurrentRegion.Resize(, 3)
End With
For i = 2 To UBound(a, 1) 'assume column heading. if not replace 2 with 1
If Not IsEmpty(a(i, 1)) Then
If Not dic.exists(a(i, 2)) Then
Redim w(1 To 4): w(1) = a(i, 1) 'Country
w(2) = a(i, 2) 'Code
w(3) = a(i, 3) 'Price
w(4) = Cells(i, 1).Address(external:=True) 'cell address
dic.Add a(i, 2), w
Else.............................
I have a spreadsheet containing a list of key fob numbers, key numbers, etc. I have 3 userforms with different for different options, i.e. Key fob no. search, key number search, room number search. I have set up the code for each of the userforms to search for the information entered in the textbox and then lists the info in the listbox which I can then click on the one of the listed items to take me directly to the place in the spreadsheet.
However since there are a range of different key numbers per key fob, I would like to know what code I can use to allow a range of information such as the key number and room number, etc to be displayed in the same listbox as the Key fob number i searched for.
Here is my code for one of the Userforms:
Option Explicit
Sub Locate(Name As String, Data As Range)
Dim rngFind As Range
Dim strFirstFind As String
With Sheet1.UsedRange
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
I cannot attach a example of my form since the site seems to have a limit on the size of the upload and my part of the file compressed in zip format is still at 168kb.
I have this code:
[Code] .....
The first change in column K it finds perfectly but then it enters a lot of blank rows and does not put blank rows at the rest of changes in column K.
I am trying to make sheet that will give me averages from different groups within a worksheet. In E64 I would like to have the total that is in I4 unless there is a total in I24 or I44. When I made the worksheet, it figures everything great but when I remove the data on following weeks it messes up the averages.
View 10 Replies View RelatedI am trying to figure out how to parse some strings. Example of strings
View 2 Replies View RelatedI 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
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.
I have a text file that has data at various time steps say 0,1,2,3 etc. In each loop, the output data is recoreded that contains numbers and strings. I want to parse certain info from this text file.
Say I am interested in reading the data for very last time step, so when I open the text file for reading, I want to move down to the Time Step 3 line. Then I want to read the text line by line, any time I encounter the word WIND, I want to skip five spaces and pick up the number that appears there. I want to paste this value to say cell A1 in a sheet called ParsedData. I want to continue reading till the end while picking all such values and filling the ParsedData sheet A2, A3, A4 ... cells till the end.
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?
if i have to search for some special character in a string and i use Instr function then which of the following is the correct way. Lets say i have to search for "****" in the string
(1)
instr(myString,"****")
(2)
instr(myString,"****")
TTHBF251863100
147943F00KNQ TTHBF251863100
REF VINS DU MONDE SA
O/RTT HBF251863100
I need a macro to transform my multiple lines cell as above ( 5 lines ) into a one line cell as below.
REF VINS DU MONDE SA
As the cells needing changing will always be different it would be good if I could have a macro working on the selected cells only, not a specified range.
I am trying to write a macro that given a selected range of cells goes in each cell and bolds the first 2 lines within the cell. An example of this would be if the cell contained:.............
View 2 Replies View RelatedIn column A I have product names, in B I have product categories and in C:H the product categories from column B are titles and the name of the subcategory is written in the cell. Because there are mutlipule sub categories that a product can belong to the report produces mirrored lines with one sub category on each line. What I need to do is combine all of these subcategory entries into one cell separated by a comer (,).
View 2 Replies View RelatedIn Sheet1 cell A1 has concatenate formula and the data source is linked from sheet "DataInput". Sheet1 cell A1 has multiple data and there are blank lines within the cell. I would like to remove these lines so it looks clean without any gaps. So basically its looking like this right now
Harry
Andrew
Joe
Apple
Billy
I want it to look like this but i am having a difficult time because this cell contains a formula
Harry
Andrew
Joe
Apple
Billy
A formula or something that would fix this problem automatically.
I have values in a column of cells in the format (All numbers in a single cell)
34567,43510,'1'
44049,99820,'1'
11124,37373,'1'
etc
up to ten such entries may appear in each cell with an arbitrary number in each cell. They were entered using the ALT+Enter method. Is there an easy formula to extract each row (such as 44049,99820,'1') to use in a formula. If there are two entries I would like to write two separate rows like:
LINE 34567,43510,'1'
LINE 44049,99820,'1'
If a macro would be required I understand and could use that also.
How to trim when there are many lines in cell. The normal trim works only when we have one line in a cell.
View 9 Replies View RelatedI have created a form for my customers. It is attached.
In creating the form, I have merged cells so that the area behind my text boxes will appear clean (no lines).
However, I cannot remove the lines around certain cells see, e.g. range K21.
I would like to make the form as "pretty" as possible for my clients (and it would help me look good to management).