I want a formula to find "#" character in a text.
Example
PO# 4343434, MINCDSD.LTD, 977766
Here i I want to find out the character "#" position
I used =FIND("#", A1,1) Answer is 3 it's ok fine.
But I have problem with this below example;
Revised PO#545455, INV# 434344 ABC LTD
In this example i want second "#" position..
I am trying to put an IF statement together. If the 7th character from the left is 5 it must show M, and If 0(zero), it must show F
View 2 Replies View RelatedCells A1:A5 all have a vlookup formula that will pull one of the following into it:
1. The word "NONE"
2. #N/A error since it doesn't exist in the table
3. A text value that has the format 13.55.46.91
I want a formula that will look at these 5 cells and give me #3. That #3 value can only appear once in the 5 cells in any of those 5. The other 4 cells will contain either 1 or 2.
3 always has length equal to 11. It always has periods in those places. The digits do change.
I have an excel file in which the cell has more than 3000 entries, like following. I would like to copy the contents before ";" to next row & continue.
Question:
CN=123,CN=12,CN=ABC,CN=AB,;CN=345,CN=34,CN=CDF,CN=DC,;CN=510,CN=51,CN=PQR,CN=PQ;
Answer should be in excel:
CN=123,CN=12,CN=ABC,CN=AB
CN=345,CN=34,CN=CDF,CN=DC
CN=510,CN=51,CN=PQR,CN=PQ
I do not know how can I achieve this through "Macro". I would like to detect ; in the cell & copy the contents before/after it in the next rows & continue till the last figure.
In column A, I have the following lines:
2014-05-15 02:08:43 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 02:31:37 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 02:37:19 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 02:37:20 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 03:07:19 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 15:01:37 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 15:04:46 @Centre INFO - CHANGE WORLD (Original World to Destination World)
I would like to use conditional formatting to highlight cells which have the same first 16 characters (yyyy-mm-dd hh:mm) before the "@" AND that contains the words "CHANGE WORLD". Therefore, I'm looking for a formula I could include in the conditional formatting so I can easily find the "CHANGE WORLD" that occurred at the same time (minus the seconds, they may vary slightly).
1) In any cell, I would like to remove "Area#xxxxxxxxx" (where x are random numbers).
Example : "INFO Log - [sys] Area#541185471Character#46545"
2) I would like to remove x characters before a word.
Example : 2013-08-28.txt@INFO
I would need to remove 14 characters before the character "@".
I tried to play with the LEN and RIGHT/LEFT formula but so far, I can't get it to work... The idea is to parse some text and remove the part in red (I was thinking about using SUBSTITUTE).
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies View RelatedI have an excel spread sheet & I wish to highlight any cell that contains any of the following characters with in a string of text.
/ : * " < > ? |
E.g. a cell in the spread sheet containing the text "Is this a Question?" would be highlighted.
I have tried to use - 'Format only cells that contain' > Specific Text > Containing > for each of the characters and this does not work.
When I do this any cell containing any text is highlighted.
In addition there is formatting on any cell > 60 characters which turns the cell red and this works fine.
What would happen if (when I get the problem above resolved), a cell is > than 60 characters & contains a character listed above?
Does 1 formatting take precedent over another?
(Not critical to know the answer to the additional question, just curious - as long as it is highlighted one colour or another then no problem.)
I am reading lines from a textfile. Each line in the textfile has the identical format: textstring1:textstring2. The two strings are always separated by the : character. I have the code to get textstring1, but because I'm a rookie, I can't figure out how to get textstring2. See the code in bold, this is the line I need to get textstring2.
View 4 Replies View RelatedI have a worksheet with several columns. I need a formula to search column D only and each time a specific location is identified to replace that location with alternate text. Example (ORIGNAL TEXT):
Column Dtext to text help.xlsx
BIRD
FISH
DOG
BAT
BUG
I need to search that listing and each time the word BIRD is mentioned have it replaced with FEATHERS and each time DOG is listed have it replaced with TAILS Final result would look like:
FEATHERS
FISH
TAILS
BAT
BUG
All other text should stay the same and replacement text should appear in the cell of the text it is replacing. This is a sheet used by multiple people several times a day and so the Find/Replace option really won't work.
Have working on this for at least 6 months and it just isn't going to happen for me. I thought I could use a Conditional format, but that is producing no results either.
I tried to writer my code myself but I have a long way to go. Here is what I must do for my case:
I want a macro to find the row that include a specific text (For example "SMSC") in a range (A1:A100) then copy this entire row below the cell which has the value "OTHERS". That is, if there are 10 pieces of "SMSC" so these rows including "SMSC" should be listed below the cell "OTHERS"
I have a table that contains various aspects of information about customer cases, and I want to replicate a user 'picking up' the case by a simple press of a button.
Users have access to only one Country, so I want to be able to search a particular column for the lowest value, but check that the Country for that row matches the user's access. If it doesn't, I then want to find the next lowest value in the column, and this is what's perplexing me??? As mentioned, I want to click a button to trigger this, and therefore want to use VBA code.
I have a column with values like this
SW SW SW CO CO PD MDM
I use this line of code to determine the text
Set rFound = Sheets("ProductCount").Rows(14). Find(What:=sProduct, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
ProdRow = rFound.Column
where sProduct contains values such as SW , CO. Now, when I use rFound to query SW, it finde me this first occuring SW. I have this inside a for loop. When it goes and queries again, it should get me the next occuring SW, not the first SW that it searched for me.
formula to find specific text from statement.
I manage to use formula find for 1 text in 1 statement, but in confuse how to get another text found if i have "8 text" to find. Generally, there will only be "1 text" for each statement.
Maybe in 1 statement there have only "IC" or "Staff Tagging" or Name" or "Product" or etc till 8.
Need formula to find a specific account no. from a range of text and return that account's value as a usable numeric.
Account number:012345678 XZ
Account value as of 12/31/2013:$12,345,56
Account number:987654321
Account value as of 12/31/2013:$876,543.21
In the attached sheet I need to check on the Destination in column B, and if it matches set criteria, divide the figure in Column E by 2. eg If LAX is the destination, then for any rows directly under LAX until the next destination appears, column E needs to equal column D divided by 2. If the Destination is not LAX, then Column E equals Column D.
the difficulty I am having is that there can be a variety of row numbers for each destination. I am having trouble creating a formula that can basically work backwards from Row-1 in column B until it gets to a text string, and check that text string against the criteria. so it may be row -1 up to about row -9 dependant on the number of flight times available to each destination throughout the week.
Could you tell me how I can find a specific sentence within a cell that contains many sentences.
for example
I want to find, "I am new." within a text that contains, "Hello I am Bob. I am new. I live in england."
I am currently using =+FIND(AB$1,$V2) where AB1 contains the sentence I am looking for and V2 contains the cell full of sentences. However this returns #VALUE! when the sentence is not found. I want it to return null.
I'm faced with receiving data for time, in a text format, as follows:
Example of possible cell contents...
20secs
40m20secs
2h40m20secs
I'm not interested in the secs value but need to isolate the h (hours) and m (mins) values to use in another formula as numbers. The time could be shown in either of the above formats with either; (a) just secs, (b) mins & secs or (c) hrs, mins & secs... and of course the hours or mins values could be 1 or 2 digits in length. How can I determine (using formula only, not vba) what the values for hours and/or mins are dependent upon what is present?
I need a macro that will look for a specific text string in the tab names of the workbook and replace it with a new specified text string (leaving the rest of the existing tab names). In other words, a simple find/replace but applied to all tab names in the workbook rather than cells. Ideally, I'd like it to pop up something and ask for the text to find and the text to replace it with, so I don't have to edit the macro itself each time I want to use it, but editing the macro each time is fine. Either way will be wonderful.
View 9 Replies View Relatedhow to work macros or VBA
I have a workbook with multiple sheets named by month and year that I use to keep track of loans I work with at a bank. In these sheets I have info such as:
Column B = due date
Column D = loan# A
Column E = loan# B
Column F = status
Column H = followup needed
(Columns A,C, and G aren't important for the current need)
What I am trying to do is create a main sheet (TRACKER) that all I will have to do is press a macro button and it will pull info for each loan that is in a pending status.
I need a macro that will search column F (Status) on all sheets and find each instance of "Pending" and once that is done, copy rows B,D,E, and H in each "Pending" instance and copy them to their designated area on my TRACKER sheet. After that is done I need it to continue to the next "pending" instance and do the same on the next available line on my TRACKER sheet.
The overall goal would be that everyday I can press the macro and it will repopulate the sheet with the current pending items (as each day I will change pending status' to complete and no longer need to track it the next day).
I already have the tracker sheet set up and ready to go with the spaces as follows:
Column G&H = Merged cells where due date will need to go
Column I&J = Merged cells where loan# A will need to go
Column K&L = Merged cells where loan# B will need to go
Column M thru S = Merged cells where followup needed will need to go
I have a client list in a table in my excel sheet. I currently enter the clients in a sheet that looks similar to a form. After the clients info is in this Form I have a button with a macro to copy the client info into a table on another sheet. This works perfectly.
Now I have another form that will retrieve the customers info in a click of a button in order to make changes to the client. Ei:I now have a second phone number and I want to update that client's info. What I want to do from here is take the info in Cell B2 (clients full Name), Do a vLookup in the "Saved Clients Table" in a Range within my table range. (Range: Saved Clients and the client's full name is in $A:$A and called "Clients Full Name" and once that name is found I need to select the cell in column C and the corresponding row.
This will allow me to copy the info in my form, Paste it in Transpose to the cell that is selected. (I already have that code)
Here are screenshots of what I am trying to accomplish. [URL] .......
I have a macro that converts all my PDF Purchase Orders to a text file and inserts the data/text horizontally into another document. However because the PDF's or the text within the PDF can be fomatted differently (that is on different lines etc) it therefore imports the information and it looks mis-aligned.I have attached a simple spreadsheet showing some sample text as it is imported and then below this how it should look like, all in line.
The range where the highlighted text in red is, is variable (but say nothing more than a variant of 10 columns). The text can also be Uppercase or Lowercase.So, I was wondering if there is macro code to find the "text" on various rows/columns and align it all in another column?
Essentially what I'm trying to accomplish is import a file from Quickbooks and determine whether it was a Credit Card, Check or Invoice based on the account number.
For example:
45-12345 should return the value CC in an adjacent cell because it contains the "-" character.
I0123456 should return the value INV in an adjacent cell because it contains the "I" character.
01234567 should return the value ACH in an adjacent cell because it doesn't contain either character.
I tried using VLookup, but I can't figure out how to write a formula for when it just contains a character, only if it matches it or is text/number.
In a cell(s) I have for example {text}dd%2BMore_Text}
what i want to do is search and replace the final }
so it should be {text}dd%2BMore_Text
the regular search and replace in excel removes all } which is not what i need.
Switched to Excel after using OpenOffice and I'm stuck on knowing what an old a 'find and replace' formula would be in Excel. It would remove a specific character (or word) ONLY if it was the last characters in a cell.
The old find and replace for open office:
Find: (.*)/$
Replace: $1
It's not that important now to delete a word, mainly the last slash '/' ONLY if it's the last character e.g. this data has 2 rows with a '/' as the last character
website.com/page
website.com/page/
website.com/page/page/
Running my old find and replace formula would remove the last slashes, but leave the others
website.com/page
website.com/page
website.com/page/page
Need simple replacement to the find and replace but a formula is also right.
I have a row of cells that contain a single letter: W,C,O.
I need to provide a count of the number of occurancies of each letter. So I have a cell that shows me the total W's, a cell showing me total C's and another showing total O's.
Whats easiest way to do this?
I know its not ideal, but on this occassion ive no desire to add and hide another column.
How to get Excel to select all cells within a specific column that contain a specific character. In this instance, I need to select all the cells which contain a comma...
And even better would be if I could get Excel to not just select all the cells in a specific column containing a comma, but each of the rows in which those cells reside.
I need to make a list of Part Numbers in quotation marks. If a number of digits of a P/N is less than 13 a number of space characters has to be added to make the string 13 characters long.
We have example P/N:
1234567890123
12345678
123456
should become:
"1234567890123(no extra space characters should be filled-13chars)"
"12345678(5 extra space characters here)"
"123456(7 extra space characters)"
Is there a function that inputs a cerain number of a specific character ("space" in this example)?
How to find and show position number? I try merged two function, but doesn't work
1º=FIND("0";B2)
2º=FIND(CHAR(1);SUBSTITUTE(B2;"0";CHAR(1);2))
Number
Position
[Code].....
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