# Find/Left Functions: Grab Everything Left Of The Last Occurrence Of "." In A String

Nov 19, 2009
I want to grab everything left of the last occurrence of "." in a string, and in the next cell everything right of the last occurrence of "."

so say the string is 111.111.1.222

column 1

111.111.1

column 2

222

my current code (which works, but its messy) for the first cell is

View 3 Replies
ADVERTISEMENT
Apr 29, 2014

I've got a long list of account numbers of varying length and ultimately need to add a dash and a dot into these numbers. Here's an example

12345678 to> 1-2345.678

123456789 to> 12-3456.789

1234567890 to> 123-4567.890

The standard format is always 3 numbers after the . and 4 numbers between the - and .

I can get to this by doing a series of functions starting with this:

=LEFT(A1,LEN(A1)-3)&"."&RIGHT(A1,3) to get 12345.678 or 123456.789

copy/pasting that value into another field and then doing this

=LEFT(E1,LEN(E1)-8)&"-"&RIGHT(E1,8) to get the results above

I can't quite figure out the format to combine the multiple steps/functions into one so that I'm not copy/pasting values and re-doing the function.

View 7 Replies
View Related
Nov 15, 2006

see attached. The only reason why I include an attachment is because the computer I am using at work is crippled and I cannot download a program which would allow me to display in the message body. A10:C12 is to be a summary of the respective values in A30:C36. I want a formula which will find the first occurrence of a revision number in B30 to B36 and apply the date in the cell to the left to the respective revision number to the above summary date column [[A10 to A12]. B30 to B36 is always unsorted order. It has to allow for lines in A30:C36 which have no entries. Line 32 and Line 33 is an example of this.

In other words, based on the above example, A10 would show date 02/01/06, A11 would show 03/22/06, and A12 would show 04/14/06. Each revision number in B30 to B36 is consistent with regard to the particular revision being the same date. The reason for wanting first occurence is because any one revision in the summary may consist of a number of revision amounts below, each having the same revision number. inally, although I would like to retain the particular column order in the example, if necessary, I can reverse A1 and B2.

View 6 Replies
View Related
Feb 26, 2014

I have two columns of data that I need to use SUMPRODUCT on. However, in one of the columns, there might be text after the number. The text can be several different characters. I only want to use the numbers, never the text. Also, there are usually blank cells within Column B and at the bottoms of both columns because this formula is going into a template for future worksheets that all have differing numbers of rows.

Example:

14T

16

40

20

150

97L

67

13

12

For the above example, I want the result to be 9879 (14*16 + 40*0 + 20*150 + 97*67 + 13*12 + 0*0 + 0*0 = 9879).

I've tried using the LEFT function to only get the numbers before any text, but I can't make it work with the blank cells at the bottoms of the columns.

View 10 Replies
View Related
May 5, 2009

Have been messing around a bit with this.

So this is the formula without LEFT

=SUBTOTAL(9,C17:C22)

i want this

=SUBTOTAL(9,LEFT(C17:C22,1))

View 12 Replies
View Related
Jul 18, 2007

I am trying to obtain price of an item using vlookup() function on the identifier 0003128 (7 digits) stored with custom format 0000000 in one worksheet. However in other worksheet, the identifier is stored as 000312 (6 digits) with text format without the last digit 8. I tried using the left(A,6) function on 0003128 but instead of returning 000312 it returns 3128 and Im unable to use the vlookup() function.

View 6 Replies
View Related
May 8, 2014

I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.

To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.

Current data structure looks like

Variable 1

Variable 2

Variable 3

[Code].....

View 9 Replies
View Related
Oct 2, 2008

XL 2003

I want to combine these three functions

=VLOOKUP(e4,AGTNAME!$A$1:$E$3086,5,0)

=LEFT(E4,FIND("/",E4)-1)

and iserror

View 9 Replies
View Related
Feb 5, 2009

I am having trouble filling a formulae series to the left on one spreadsheet, the fomulae being references to another sheet.

For example, I have two sheets 'Mtce Options' and 'Base Case'. In 'Mtce Options' I have the following formulae

A B C

1='Base Case'!A15='Base Case'!D15='Base Case'!G15

I want to fill to the left, incrementing the column references by a factor of 2 each time, eg. next two should be ='Base Case'!J15 and ='Base Case'!M15.

However, if I autofill to the left by highlighting A1, B1 and C1 or just B1 and C1 all I get is an inappropriate reference such as ='Base Case'!D15 or ='Base Case'!F15, respectively, in D15.

View 2 Replies
View Related
Aug 3, 2009

I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:

View 2 Replies
View Related
Jan 22, 2014

this formula as a result =IF(LEFT(A3,1)="0",RIGHT(A3,LEN(A3)-1),A3) It worked great to strip the left most zero from a string of text numbers in the cells where the formula was applied.

However, now I need to put the zero back in lol!

So...how would I adjust the formula (or write a different one) to insert a zero as the first digit of a string of text digits?

View 4 Replies
View Related
Feb 12, 2010

I have a issue with VLOOKUP and LEFT STRING ETC I have a lookup table see below

AB10 Grampian

AB12 Grampian

AL8 Anglia West

AL9 Carlton LWT

B1 Central West

B10 Central West

B11 Central West

B13 Central West

B14 Central West

B15 Central West

View 9 Replies
View Related
Dec 4, 2013

I have a table of data that I need to find the leftmost and rightmost number in that are greater than a specified number in another cell. I've attached a spreadsheet to show what I've got to work with.

I need to do this 1 row at a time and have the outcome in descending cells on the right hand side of the 2 example rows.

View 7 Replies
View Related
May 22, 2013

I want to select email addresses in a particular cell. Since every email address will contain @ so I want to extract the email address from the cell on the basis os all the characters to be selected on the left & right of the '@' before a space is encountered.

Eg

If the cell contains the below values:

91 121 5158123 / 5159123, 011 5103710

91 999 999 1123

info@designs.com

I only want the email address to extracted to the next cell. i.e. info@designs.com

View 9 Replies
View Related
Feb 7, 2014

I'm trying to use logic to identify trends...in each of the strings below, I want to count the occurrences of the left most character appearing consecutively. The answer is to the left

I'm trying to do this via a formula vs. vba if possible

BBBBBBBBBBBBBBBBBBB - 19

BBBDUDUUUUDDBBBBBBB - 3

UUDUDDUUUDUDBBBBBBB - 2

UDUDDUDUUUDDBBBBBBB - 1

[Code] .....

View 3 Replies
View Related
Nov 26, 2008

Obscure lines of info which I'm trying to break up into pieces. Being able to find from the right instead of left would be REALLY handy. See attached example with details and example!

View 3 Replies
View Related
May 2, 2014

Cell A1 = 78-36

This is a win/loss ratio so to have it as a percentage I would have to divide the first number by the sum of the two numbers so 78/114 = 68.4% How would you do this with a formula?

I came up with this that isn't quite right, because RIGHT and FIND don't work together.

=LEFT(A1,FIND("-",A1,1)-1)/(LEFT(A1,FIND("-",A1,1)-1)+RIGHT(A1,FIND("-",A1,1)-1))

There must be a smarter way of doing this. TY

View 8 Replies
View Related
Nov 30, 2006

I have some code that run on Excel 2003, and fail on Excel 2000. It happens on functions Left, Right, Mid and Trim. I've found that I must use in "Late Bindings".

If exist any convertion for above functions?

View 6 Replies
View Related
Feb 21, 2014

I am compiling a database of tennis stats for various players. I input the set scores (like 6-4) and I then need excel to find "-", go to the left and right, and add up all cells containing these scores on a full row.

For example:

6-4, 6-4, 6-4 would become 18-12

I can do this by using "find" and left/right. The problem is, the more records I add, the more I have to keep changing the formula.

[Code] .........

Is there a faster way to do this, preferably without using macros.

View 14 Replies
View Related
Feb 22, 2008

I have the name bloggs, Joe in cell A1. Is there away to flip the name around and get rid of the comma.

i.e. Joe Bloggs

View 9 Replies
View Related
May 25, 2007

Got the following formula:-

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

I need to add the SUBSTITUTE function to this but can't figure out where it goes if somebody could point me in the right direction please? My substitute formula is SUBSTITUTE(L5,"car","train").

View 4 Replies
View Related
Oct 27, 2008

I am trying to find a code that will simply find the last cell in Column A of a named Range (titled: ConditionlFormatArea)?

Also IF I could get a line(s) of code that would just find the last cell in Column A of the Used Area.

I ask for both because I am not yet certain which line of code will work best for me.

When I have that cell located I need to Remove the Conditional Formatting. I think I can get the code for that part.

View 9 Replies
View Related
Nov 12, 2013

I understand that the function FIND(find_text, within_text, start_num) will find the first occurrence of a stated string. And by adjusting the start_num, you can skip a set number of characters before the search begins.

But, is there a way to find the Last occurrence of said string? In my case the find_text string may occur multiple times in the variable length within_text cell to be searched.

Sample: Find the last occurrence of MDU

Code:

Sample string1 - ABDJ FFU MDU WWW AEH JJF MDU JJI LLK OOI HAHA QWAS

Sample string2 - HFUR MDU HDS IGH RJR KDHF LLDE MDU KKJ MDU GGG

In the above 2 sample strings the output of the Find() function needs to be 26 & 40. Having a cell formula would be desirable, but if it can only be done by VBA that is acceptable, also.

View 9 Replies
View Related
May 21, 2014

Im trying to pull together a formula to achieve the below. What i have is a spreadsheet to be used by staff in different countries so im looking to make it easy to modify the categories used without the user needing to do anything complex with formulas.

The right hand side of my file (C:E) shows a code for each year we have covered a country with the year as a prefix (2014 - Blah). The left (A:B) is a summary showing the latest year we covered that country.

Current formula:

{=IF(COUNTA(C2:D2)<=0,"","β-"&MAX(LEFT(C2:D2&"0",4)*1))}

The current formula works fine if you set the columns to where the data actually is but for ease of use i need it to look at the data in rows 1:2 and match up the Continent/Country and then MAX the dates.

View 1 Replies
View Related
Dec 9, 2013

I'm looking for a way to split a cell with text into two cells. The first cell can only contain up to 40 characters, the rest needs to go in the second cell. However, I don't want the text to be split in the middle of a word. So basically, if the text is in cell A1, I need to find the closest space to the left of position 40 in A1, and move anything to the right of this position to B1. Example:

Original text:

A1: One green apple and a bucket of small onions that smell nice

I want to avoid this:

A1: One green apple and a bucket of small on

B1: ions that smell nice

I want to achieve this:

A1: One green apple and a bucket of small

B1: onions that smell nice

View 4 Replies
View Related
May 23, 2007

I would like to find the position of the last occurrence of a character in a string. For example, I have a string with the following:

"c:wwgpeToolbidsTest File.GP$".

I would like to find out the position of the last '' in the string because I want to pull the filename, 'Test File.GP$' into a variable. I would like to avoid writing code to do this. Are there some Excel functions I can use for this?

View 7 Replies
View Related
Mar 30, 2008

Im going to try to make this as clear as possible. I cant use my actual data because it wouldnt make any sense to anyone so Ive made up an example problem. Here goes...

Lets say in Sheet 1 I have two descending columns of data. Column A is MODEL of Vehicle (Civic for example). Column B is vehicle identification number (xxx for example). Sheet two has 4 columns of data, but only one is really required for this example. Cell A1 is the MAKE of vehicle (Honda for example). Directly below that in Cell A2 is the MODEL of the vehicle (Civic). There are then a few rows of empty space until it gets to the next vehicle MAKE and MODEL.

So in Sheet1 there is a long list of MAKE's in ColumnA and VIN's in ColumnB. Sheet2 Has a long list of MAKE's and MODEL's in ColumnA and random data in other columns.

What I want to do is assemble a Macro to start in Sheet1-A2, read the MODEL then copy the corresponding Vehicle Identification Number in B2. I then want it to go to Sheet2-A2 and start searching downward until it comes across a matching MODEL. Once it finds the match I want it to step downward 2 cells and paste the Vehicle Identification Number. Then return to Sheet1-A3, and repeat the process until EOF.

View 9 Replies
View Related
Sep 16, 2013

I have a column that is giving unwanted value . dont know the reason as that excel file has been created by some other guy and I just started working on it .

My Question is how to move to 2 cells left(A for example) from from that unwanted value column. and check if

A is equal to cell above it , means B Cell(Row above A but same column).

As my excel file is totally based on Forms, Macros, I am not quite familiar with macros.

Is there any way to put if condition in one cell (column) and drag it all the way down which should work for all the values in these 3 column.

And also if A=B then I want to make that unwanted value cell="".

View 1 Replies
View Related
Sep 9, 2013

I am trying to find the median of a set of numbers on a column that correspond to dates on the left column. I want a monthly median average of the numbers on the right which correspond to the dates on the left. So for example. I want to make an equation that gives me the median of all the numbers on the right if they fall within the range of a certain month(in this case October). I've tried These:

=IF(COUNTIFS(A:A,">=10/1/12",A:A,"

View 4 Replies
View Related
Mar 1, 2012

Items in Column A1 are calculated by (B2/4+5)*1.4 Items located under the columns 2000, 3000, 4000, etc... 10,000 are calculated by taking the top number, eg 2000/(A1 cell value)+the column B number. 2000/7+0 = 286 (rounded numbers)

I need to find an way to look up for x number (2000,3000,4000, etc...) find the smallest number in that column and then return the value in column A1.

Cell

A1 Number >2000300040005000600070008000900010000

70 2864295717148571000114312861429

84 24236148059971883795610751194

[Code].....

View 2 Replies
View Related