Formula To Extract All Characters After A Certain Number Of Forward Slashes From The Right?

Apr 9, 2014

I'm working with a large list of items of varying length. A sample worksheet is attached. I need to do the following:

1) If the characters after the final forward slash from the right are of the pattern "tttxxx" (where "t" is text and "x" is a number/digit), then return all characters to the right of the final slash, including the slash itself:

Y:/Series/hun/hun109 ----> /hun109

2) if the characters after the final slash from the right are not of the pattern "tttxxx", then return all characters after the second slash from the right, including the slash:

Z:/Series/crd/crd409/editorial ----> /crd409/editorial

3) if there are less than 3 backslashes in the item, return a blank.

View 3 Replies


ADVERTISEMENT

Move From Seperate Columns On Sheet2 To One Column With Forward Slashes "/" Between Sheet3

Jan 11, 2010

I have three columns (9, 10, & 11); text, currency, and text and I am trying to move them from seperate columns on Sheet2 to one column with forward slashes "/" between them on Sheet3. The attached code does this, however I lose the dollar sign and commas.

View 4 Replies View Related

Extract The First 5 Characters In A String, And Convert Them To A Number

Nov 7, 2008

Say I have a string "09800EBHR052708"

How would I take the first 5 characters and get a number out of them?

View 14 Replies View Related

Extract X Number Of Characters Followed By Specific Character

May 23, 2008

How can I extract mid section of the string which is always 5 characters long and is always followed by a period "." ?

My account strings are in 3 sections (but the third section isn't always used)

For example,
1.10210
1.22556.001
900101.56201
955261.54444.001
5566625.58886.957756

View 4 Replies View Related

Explanation For Formula That Counts Number Of Numeric Characters

Nov 21, 2012

The formula below was posted recently as a way to extract a numeric substring from somewhere in larger string. When I first saw this formula, how it "came up with the goods" and so I spent some time looking at parts of it to see what they did.

Although I've made some progress, I still don't fully understand it. Heres the formula, and below Ive detailed how far I've got:

=Iferror(lookup(9.99e+307,--mid(a1,min(find({1,2,3,4,5,6,7,8,9,0},a1&1234567890)),row(indirect("1:"&len(a1))))),0)

I hope my findings below are correct:

1. The formula is returning the first substring of contiguous numeric characters from the full string in Cell A1
2. 9.99e + 307 ensures that the search value won't be exceeded by the returned value.
3. The Mid function uses Min(Find( to get the character position of the first numeric in A1 (so MID then has its first 2 parameters). So far so good.
4. Now... I cant work out where MID parameter 3 (the substring length) comes from. I suspect its the row function, but can't see how as this isn't an array formula, and without CTRL+Shift+ Enter, I could only get Row to return 1 in my experiments (giving me only the first substring digit).

How does the formula return the correct number of numeric characters, (apparently) without counting them?

View 3 Replies View Related

Formula To Extract Number Only?

Apr 16, 2014

I have list of item numbers which some of them contain letter "C" or "S" at the end. For example,

8002
8002C
8002S
85555C
825S

Is there a way to select number only?

View 2 Replies View Related

Formula To Lookup The Numbers From Above And Only Extract A Number Once

Nov 27, 2008

I have a spreadsheet on a weekly data with diferent reference names
e.g.
101
101
101
105
105
101
101
109
109
114
114
101
101

what I want is a formula to lookup the numbers from above and only extract a number once
e.g
101
105
109
114

View 9 Replies View Related

Formula To Extract Undefined Number Of Names Following Specified Character

Apr 8, 2014

I need a formula which can pick out the names from string of text, each name follows "-->", the number of names in each cell differs and is undefined. The result needs to be posted into each column following these cells.

An example of a cell is below:

"W/L FIGHTER Str TD Sub Pass EVENT METHOD ROUND TIME

Loss -->--> Tom Aaron -- -- -- -- Strikeforce - Hen... U. DEC 3 5:00

-->--> Matt Ricehouse -- -- -- -- Dec. 4, 2010

Win -->--> Tom Aaron -- -- -- -- Strikeforce - Hea... SUB 1 0:56

-->--> Eric Steenberg -- -- -- -- May. 15, 2010 Guillotine Choke"

I have tried using text to columns but everything after the first line is not recognised (when I click finish anything after "TIME" is simply not there).

Example attached : UFCv1.xlsx

View 7 Replies View Related

Extract Numeral/s Between Characters?

Dec 29, 2013

I would like to extract any numerals between the comma and colon. (Format is text)

Example: 8 Dec, 1:52PM (1 to show in another cell)

Format is always the same but date/day & hour may change. (Hence changing number of characters depending on day and time.)

Example: 11 Dec, 10:49AM (10 to show in another cell.)

View 2 Replies View Related

Extract Data Between Two Characters?

Feb 3, 2012

In column M I have data that lists a number, then number of metres between brackets. For instance

Column M
11 (79m)
1 (5m)
22 (123m)

Basically what I want to do is to always return the number of metres without m, so the data between "(" and "m".

I tried to do this using the find and mid formulas, but I either haven't got it quite right. I'm only after a spreadsheet solution (not VBA).

View 6 Replies View Related

Extract Characters To The Right Of Symbol

Oct 8, 2013

Data in field A1 currently appears as below.

8/10

I want to extract everything to the right of the "/" in a new field.

The formula that I am using is =RIGHT(A1,FIND("/",A1)-1)

For some reason, I am only receiving a 0 in the new field and not the 10 that I expect.

What's odd is that the formula is working as intended on every other field except this one.

View 6 Replies View Related

Extract Numeric Characters

Jul 1, 2009

I have a column contains Postal Adress in more than 5000 rows. Column contains Door Number, Area, City and Zip code. I need to separate "Zip code" alone in next coulmn. Zip code (of India) will be in six digits like "600083" also some cases contain space in middle of zip code like "600 083" (after 3 digits). Is there any way to do this without doing cut & paste?

View 9 Replies View Related

Extract Set Of Numeric Characters

Jul 3, 2009

I have a column contains Telephone numbers with or without area code and country code for across the country (India). I need to extract the telephone numbers alone (neither area code nor country code). Telephone numbers will be 6 or 7 or 8 digits (not more than that). Is it possible to extract any set of numbers contain 6 or 7 or 8 digits continously? Some of the cases contains 2 contact numbers (2 set of 6 or 7 or 8 digit characters, between special characters will be there like slash, comma, space, hypen, etc.,

Here are some examples: ....

View 9 Replies View Related

Extract First & Last X Characters From Cells

Sep 28, 2006

Column A in Excel has loads of numbers all in this format 971-417. I need to have each of those two numbers in a separate cell from each other(and without the hyphen of course).

Column A Needs to be: Column A Column B
971-417 971 417

In short, I'm looking for a quick way to put each number in its own cell for hundreds of rows. Are there formulas I can use to do this or does it have to be done slowly, one at a time, stripping the information from one cell to another?

View 6 Replies View Related

Extract Characters From Phrase

Nov 15, 2006

i have a list of information, and from that i want to extract a certain piece from that information and out it in a new cell...
eg. 'KLM00506', 'KLM00409-10', 'KLM00821-9' etc

as you can see, the info is not in the same length
what i need is to take out the FIRST 6 characters and have the remaining to be in a new cell

anyone has an excel formula that i can use ? not really interested in code, cause in this particular worksheet, everything is at its' place, no code required

View 4 Replies View Related

Dates And Slashes

Jan 18, 2005

i would like to input say 201005 in a textbox and on exit it should read 20 / 10 / 05 ie dd / mm / yy
how this can be achieved using VBA.

View 3 Replies View Related

Take Slashes Out Of Dates

Jul 6, 2006

Is there anyway to take the slashes out of the dates and keep the same number format? example...11/11/1974 should then = 11111974

View 4 Replies View Related

Extract Text Characters Only From A Cell

May 6, 2009

This is very similar to my previous post, which was solved. Now that I've extract the numbers, I need to extract the text for the specific work activities, for example 13Z or 9GGG. I'm assuming some variation on this formula:

=LOOKUP(9.99E+307,--MID(C4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},C4&1234567890)),ROW(INDIRECT("1:"&LEN(C4)))))

is the solution, but I'm struggling with making the correct alterations.

View 2 Replies View Related

Extract Text Separated By Characters

Apr 24, 2009

I need a Excel 2007 formula that will extract a string of text that is seperated by characters.

Example:

in Cell (A1) I have the following

ALA 11 : [ MOBILE ] 36619 1/2 ... HOME ... JUNE 2009 / TODAY: $ 50000

End Result wanted:

Cell (A2) 11
Cell (A3) MOBILE
Cell (A4) 36619 1/2
Cell (A5) HOME
Cell (A6) JUNE 2009
Cell (A7) 50000

View 8 Replies View Related

Extract Trailing String Of Characters From A URL

Dec 23, 2009

I have a problem that I just don’t seem to have the brain power to solve right now. I have a list of websites and I need to extract all characters after the last “/” in the URL. URL example:.....

I have been using the find function in conjunction with LEN and RIGHT etc but the multiple instances of “/” is causing headaches for me. Also, the trailing string of characters that I want to extract can be both text or numeric and are of differing lenths.

View 3 Replies View Related

Extract Left Most Characters Until A Space

Oct 6, 2006

I am trying to write a function that finds and breaks a cell about the first space.

Worksheet is:

=LEFT(name,FIND(" ",name)-1)

Have tried...

Function Firstname(name)

Dim Space As Integer

Set Space = .Find(" ", name)

Firstname = Left(name, Space)

End Function

and also...

Function Firstname(name)

firstname = left(name,find(" ",name)-1))

End Function

View 6 Replies View Related

Extract Left Characters Up Until Specified Text

Nov 10, 2006

I am attempting to utilize the Mid, Left, and Right functions to parse out data returned in a single cell. There are twelve months of data returned with the "title" listed after. I would like to be able to FIND the title and return a specific number of characters of data to the LEFT not the right (as Mid seems to do).

EX.

Cell A1{ 1 2 4 5 3 5 TITLE1 1 2 3 6 7 4 5 TITLE2 3 4 5 2 4 5 TITLE 4 }

Cell A2{ 1 2 4 5 3 5 TITLE2 1 2 3 6 7 4 5 TITLE3 3 4 5 2 4 5 TITLE 4 }

I would like to be able to search each cell for the "TitleX" then parse data out to the left of that title (and if not found, return "").

There are too many different Titles to use IF statements and I cannot change that the data comes to the left of the title.

View 9 Replies View Related

Extract Numeric Value Based Upon Ending Characters

Jan 10, 2014

I have a list of numbers in which I need to return the last 5 digits; the length of the string may vary.

However, if the last two digits of the number ends in a specific value then it should skip over the last two numbers and return the preceding 5 digits.

I have tried and have used the very basic Left, Right and Mid functions. My problem is I dont know how to code the formula to identify the last two digits and skip over them, if necessary.

I have provided an attached example.

Extract numeric value based upon ending characters.xlsm

View 5 Replies View Related

Alphanumeric Characters Extract Only The Numerics From Cell

Jun 12, 2008

I have the following in Column A

28VNC
JR2KL
29C9O
PQS11

I'd like have this result

28
2
299
11

How would I achieve this.

View 6 Replies View Related

Extract Last Letter And Remaining Characters From Alphanumeric String

May 14, 2013

Having trouble with this one. Searches seem to bring up every other variation of extracting info from strings except this.

I have cells which contain alphanumeric strings as below and, using a formula, I want to extract everything from and including the last letter to give A 2-3, B 3 and C 3-4 in the examples below.

Sub-base A 2-3
Sub-base B 3
Paving C 3-4

View 7 Replies View Related

Extract Characters To The Left Of Space Character In A Cell

Sep 10, 2013

I can do this in Excel, but I don't seem to have a single example to hand of how, using VBA, to extract all characters up to but not including, the first space character in a cell.

View 9 Replies View Related

Convert Date Separators From Periods To Slashes

Jan 8, 2008

I have spreadsheets with a lot of dates where the date separators used are periods “.” instead of slashes “/”. For example, 12.02.2008 is what shows in a cell. Where I live (Australia) that means 12 Jan 2008. My need is to have Vba code that converts these to dates; e.g. 12/02/2008 or 12-Jan-2008, etc. I can manually select the cell or cells, press Ctrl+H, replace the period characters with slash characters and it works correctly - I get 12/02/2008 – which I can then manipulate as a date. However, if I record a macro of the above actions, when I run the code the result I get in the cell is 2/12/2008 – i.e. 2 Dec 2008. The code recorded is:

Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The only time the code gives the correct result is when the month is greater than 12; e.g. 23.02.2008 is converted to 23 Feb 2008. My computer is set up with regional settings to Australia and date format day/month/year.

View 3 Replies View Related

How To Carry Last Entry Forward

Apr 30, 2012

below fig 1 is a part of a worksheet we are using for Stock. The idea is to track stock write downs on a yearly basis. Where I am struggling is a formula to carry the stock balance and value forward if there has been no movement in a given year.

The enteries in green (fig1) colums "Stk Forw" & "Car Fwr" represent how I would like the info to look.

Is there a formula that could look for the last entry in "Stk In/Out" if the column "YTD to date" is empty and carry that value forward to the next row.

I Would also need a formula to sum the Total & Car Fwr colums as one total.

Fig 2 Shows the formula I am currenlty using.

View 1 Replies View Related

VB Backward & Forward Loop

Dec 17, 2008

I'm writing a VB macro that will find the start time and finish time of a job and then work out the time it took to run.

So for example:
D E
09:53 wsp285A 09:53 wsp285A 09:53 wsp285A 09:53 wsp285A 09:53 wsp285A 09:59 wsp285A 09:59 wsp285A 10:06 wsp285A 10:06 wsp285A 10:13 wsp285A 10:13 wsp285A 10:20 wsp285A 10:20 wsp285A 10:26 wsp285A 10:26 wsp470a 10:29 wsp470A 10:29 wsp470A10:29 wsp470A 10:29 wsp470A 10:29 wsp470A 10:29 wsp470A

In the above I want to work our how long job 285A takes, I will need to find the first instance of the job by looping forwards through the data, and the last instance by looping backwards, then taking the last time away from the first.

The only problem is, I have no idea how to write this in VB!!

The job name is always in column E
The time is always in column D
I want to paste the time taken in for arguments sake in cell E1 of the current worksheet

View 9 Replies View Related

Character Extraction: Extract The Characters "9078.314" And Put Them In A Different Cell

Feb 5, 2007

Cell R2 contains this value: " Forecast Time: 240 Index A forecast = 9078.314". I want to extract the characters "9078.314" and put them in a different cell. The cell that it should be affected to depends on the characters "240". I believe I should use the MID() and VLOOKUP() functions but I get stuck at the very first step of extracting my data.

View 3 Replies View Related







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