Return Data Infront Of Specific Letter In Cell

Jul 14, 2008

ColA contains Purchase Order (PO) numbers and I want to return the department number or project number (which is contained within the PO) in ColB eg.

A B
PO # Department/Project #
AP001234P1234 Want to return everything before the 'P'
PR004444P4444 Same as above
TR2008-1234 No need to change
6501P1234 Want to return everything before the 'P'

I was using IF function but there are too many variables then I thought about the 'P' but not sure how to go about it?

View 10 Replies


ADVERTISEMENT

Return Column Letter Based On The Letter In A Cell.

Dec 31, 2009

For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).

When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.

=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")

View 9 Replies View Related

Seach For A Specific Letter In A Cell

Oct 6, 2006

Does anyone know the VBA line for finding a specific letter in a cell?
eg.

Say cell A1="B"

Say cell C3 contains a word, and I want to know if that word contains the letter shown in in cell A1 (ie. contains the letter B).

View 3 Replies View Related

Find/Return Nth Letter In Cell

Jun 12, 2008

I'm trying to write a macro where I match specific letters from one cell, with a column in another sheet so I can copy/paste adjacent cells. As an example I have a list of part numbers in column A of my Document1:

ex.
C12932-1
C13226-2

I want to be able to find the corresponding part number in column A in Document2:
ex.
C-12932
C-13226

However the proplem is clearly that the part numbers are written differently. So I need to just search for "12932" or "13226"

View 3 Replies View Related

VBA Change Cell Value To Specific Letter Based On Value Of Different Cell In Different Worksheet

Jun 6, 2014

I've done the following:

Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub

However, this doesn't even work.

When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...

View 5 Replies View Related

Display Chosen Number If Cell Text Begins With Specific Letter?

Apr 3, 2014

I'm trying to write an IF formula that will return a number if the word in the adjacent cell begins with a specific letter. Here's what I want to show:

City
01

Express
02

Overnight
03

So "C" would return 01, "E" would return 02 and "O" would return 03.

View 3 Replies View Related

Search Box / Dropdown To Return Specific Data

Nov 15, 2013

I have a workbook with several sheets of data - it's basically names with a series of assessment data. Each name and selection of data is in its own row.

I want to build an additional sheet with a search function/drop down box that wil allow any name to be searched for/selected and return that specific name/all/specific cells in that row. I believe this could be done either using VLOOKUP or a Macro but I am only a beginner with either of these things.

View 7 Replies View Related

How To Return Data With Specific Date In Macro

Jun 1, 2014

I have my tracking data sheet in my master file.

I wish to create a macro code to only retrieve the data on the file last modified date with the specific date.

For example like this :

I wish when I click on the List button, it will pull out the file that is from 28/5/2014 till 30/5/2014 which I have input in B6:B7

I have my code like this but it will pull out all the file in the folder:

Code:
Dim iRow

Sub list()
iRow = 11
Call ListMyFiles(Range("C4"), Range("C5"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)

[Code] ........

View 1 Replies View Related

Return A Specific Value Into A Cell

Jul 31, 2009

In the attached document i need to work out the following solution for a formula but im baffeled! In the completed column i need to show 'ongoing' if there are any 0's in the 101 - 104 columns and 'completed' otherwise.

View 3 Replies View Related

Return The Letter Value Of A Column

Aug 28, 2008

When I used "activecell.column" it returns the numerical value of a column (For Example: Column B would be 2).

I use the following in my
Dim StrRef As String
StrRef = "AN"

I want to be able to refer to column "AN" without having to hardcode "AN" into my code becuase it may change.

Below is part of the Code where I refer to "AN" in context of a loop---

For Each icell In ActiveSheet.Range(StrRef & "7" & ":" & StrRef & LastRow)

I tried Activecell.Address but that return the entire cell reference.

View 9 Replies View Related

Return Last Used Column Letter

Apr 22, 2008

I need to find the last used column in the excel sheet. I used the below mentioned code to find the last used column but it gives the ouptut in integer.. for eg) output is 13 if the column is M, but I need to get M as output.. how do i do that

Sub FindLastColumn()
Dim LastColumn As Integer
If WorksheetFunction. CountA( Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
MsgBox LastColumn
End If
End Sub

View 3 Replies View Related

Pt Private Infront Of Sub

Jan 13, 2009

My nacro wont work if I put private infront of sub, if I take out private it works. Its not connected to any object, just simple copy paste

View 9 Replies View Related

Excel 2010 :: How To Return Value Of Cell Beneath A Cell With Specific Value

Jun 14, 2014

What I'm trying to do is, create a formula that works like vlookup, except I want to return the value of the cell that is a specified number of cells below the looked-up data in a single column array. So, let's say cell A1 reads "Bacon". In B1, I'd like to put a formula which searches the array for the cell that contains "Bacon" and then returns the value of the cell underneath, let's say the value is "Eggs". Then in C1, I'd like to put another formula which also looks up "Bacon" in the array, then returns the value of the cell that is down two cells from the cell that contains "Bacon", let's say the value is "Milk". And then in D1, a formula that returns the value 3 cells below "Bacon", and so on. All the values here will be text, and not sorted in any specific order. I'll be using Excel 2010.

Please take a look at the attached sheet if it clarifies things.

View 3 Replies View Related

Capitalizing First Letter Of Specific Word In A String?

Oct 2, 2013

I have sample string: Cat jumped over lazy Dog

I only want to capitalize: LAZY

so it should be: Cat jumped over Lazy Dog

Proper capitalizes first letter of every word, just want one word capitalizaed.

Also, if I want to use an and function to capitlize multiple words, so it should be: Cat Jumped over Lazy Dog

where just Jumped and Lazy are capitalized.

View 5 Replies View Related

How To Delete Specific Signs Or Letter At The END Of A Column

Nov 29, 2007

I need a macro, which removes spaces, symbols, letters, numbers or combination of them from the end of all cells in a column. The symbols should be chosen before the macro starts its work.

For example:

Before running the first macro:

Column A
Word1;;
Word2;
Word3
Word4;

After running the macro the end of the cells should be cleaned from chosen symbols, spaces or signes. (in our example if a cell ends with sign ";" then these signes should be deleted).

Column A
Word1
Word2
Word3
Word4

View 10 Replies View Related

Column() To Return A Letter Instead Of A Number

Feb 14, 2006

Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible?

=INDIRECT(row() & column())?

View 14 Replies View Related

Return The Largest Letter In An Array

May 15, 2007

how I can return the largest letter from an array of letters? e.g. if the array is "A,B,C,D,H,K,L,O,P", the value returned would be P. Kind of like a MAX function, but for letters instead of numbers.

View 9 Replies View Related

Return Column Number Not Letter

Sep 4, 2006

How to return column number (not letter)?

View 5 Replies View Related

SUM Until Specific Cell Is Reached And Heading Return?

Nov 18, 2013

I have a number of dates (columns) and under each date there is the demand value (Rows). Also, i have a column that has the current inventory. what im trying to do is to keep on adding the demand in one row (i.e multiple dates) until the sum just exceeds the inventory. After that, i would like to return sum the date at which we stopped adding. the point of this excercise is to see at which month will our inventory deplete according to the demand. Below is an example solved by hand.

Demand
Part #
month 1
month 2

[Code].....

View 9 Replies View Related

Specific Cells Are Formatted To Start With A Capital Letter

Jan 3, 2008

Is there any way of writing a macro so that specific cells are formatted to start with a capital letter and end with a full stop. Can you also write a macro to automatically spell check specific cells?

View 11 Replies View Related

Find And Replace A Specific Letter/number Combination

Jul 9, 2008

I have a column of references I wish to standardize. Contained within a general text description there is also an order-specific reference number, which is not relevant for my purposes. I wish to find all of these numbers and replace them with nothing (i.e. retain the rest of the description).

The reference numbers are always in the format "P#####/##". Unfortunately these references are in the middle of the text field, not at the start or end, so I can't use a LEFT or RIGHT formula to delete them.

Once these reference numbers have been deleted I will then be able to filter for unique records only. When I do this at the moment the filtering has no effect due to these specific reference numbers.

View 9 Replies View Related

Return Value Of Specific Date That Is On The Calendar Into Respective Cell?

Dec 14, 2013

Basically, it is a scheduling of approving or rejecting of request dates. whereby, i will receive the request dates from Google doc form and then i will extract the data from web into the excel. The data i receive will include the ID, Date request and Remarks.

With the Information in hand, the data will then be "plot" into a schedule (which is a year calender that i have created on excel) base on the ID requested from the web and the ID in the excel.

After plotting, i also want to check if the number requested date exceed the quota set PER DAY, if it exceed, then i have to reject, if its not then approve.

So here comes the problem.. After all the dates have reflected on the calender, I want to get the information from the calender result(which is whether the date is approve or reject) back on to respective ID and Dates requested.

I have attached a example excel for clearer view of my explanation.

View 6 Replies View Related

Lookup Date And Return Specific Cell Value From A Range

Jan 31, 2014

I have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.

However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.

View 3 Replies View Related

Formula To Return Specific Value If Cell Falls Within Range

Apr 6, 2012

I am looking for a formula that will enable me to determine which one out of five thresholds a percentage falls within and the retuns a specific value for the corresponding threshold. Here is an example of the data (only showing three thresholds):

_____A________B_________C____
1 ___0.0%_____50%_____$5.00
2 ___51%______75%_____$15.00
3 ___76%_____100% ____$25.00

Where the values in A1 and B1 are the low and high ends of one range. I would like the formula to determine which range the value falls within and return the value in collumn C (C1 for the A1:B1 range).

I can do this using multiple 'if' statements, but am looking for a much more streamlined way to determine the proper value in column C.

View 2 Replies View Related

Formula To Return (1) If Any Value In A List Exists In Specific Cell

Oct 24, 2013

I am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A

For Example:

Sheet 1
Column A
Apples
Oranges
Banana
Fruit Salad
Fruit Smoothie

[code].....

View 6 Replies View Related

Inserting Text Infront Of Numbers

May 18, 2009

Yesterday I got the solution to insert the text by using custom format. Exampe: 112233 to Ab-112233 by using "Ab-"General

But when I tried the same method to inserset the Ab on 11-1122

Like 11-1122 into Ab-11-1122 in same cell, it doesn't work.

View 6 Replies View Related

Excel 2010 :: SUMPRODUCT Answer To Return Blank When No Data In Specific Cells

May 12, 2013

I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.

Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6

Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0

The formula I am using is:

=(SUMPRODUCT($A$6:$A$15,C6:C15))/$A5

Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.

Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.

My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?

View 2 Replies View Related

Search Row For Text And Return Column Letter Of First Occurrence?

Dec 14, 2011

Is there a way to have a formula search an entire row containing certian text, for example "x" and have the formula return the letter of the first column that text is found?

View 8 Replies View Related

Does A Property Exist To Return The Letter Reference For A Column

Jan 24, 2008

I need to build a formular in a cell that uses various columns in it's row such as: =A2+A4+A17+A24. In the past I've used simple number references to the columns (a is 1, b is 2,.....) I can't do tht though if I'm using a macro to automate the creation of a formula, right?

Is there a property I'm missing that will return the letter reference to the column instead of just the number as Activecell.column does?

My alternative is to write my own code to do this, but you know the bit about not reinventing the wheel....

View 9 Replies View Related

Return Number Based On Letter Position In Alphabet

Aug 27, 2007

i wish to number the cells bases on the cell on the right.

Heading
A
A
A
A
A
A Total
B
B
B
B
B Total
C
C
C Total
D
D
D
D Total

to

Heading
1A
1A
1A
1A
1A
1A Total
2B
2B
2B
2B
2B Total
3C
3C
3C Total
4D
4D
4D
4D Total

The values in place of A,B...can range between 1 to 6.

View 9 Replies View Related







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