Free Microsoft Excel 2013 Quick Reference

Find Text Between Characters And Remove Hyphens

I need to make a macro that will find text between "o/" and "/", remove hyphens from the text it found, and then add it to the end of the current cell contents.

I know how to add to the end of current cell contents, but cannot figure out how to grab text between certain characters or replace hyphens and replace with spaces.


Post your answer or comment

comments powered by Disqus
I need to find the text in a cell between two special characters using vba. Here is a sample that I need to do that to:

I need to find the 01 but it can be anything in there (text or numbers and varying sizes but the text will always be the same
in relation to the two special characters)

Hi guys, I'm looking for a formula that will remove the text between the first and last space in a cell e.g. 'Mr Brian M Taylor' becomes 'Mr Taylor' in the cell next to it.

Ideally I'd like it to work when there's only 1 space as well e.g. 'Mr Taylor' displays as 'Mr Taylor' in the next cell using the same forumla as the first example.

This is a bit advanced for me (but learning all the time :D) so any help would be fantastic.
Cheers, James.

I need to find a Text, set in cell e.g. ("A1"), within various strings in a certain Range, e.g. ("B1:K20") /there are different strings by long in cells in the Range, but always contain one or even more times text set by A1/, and Replace this Text with the other Text, set by cell, e.g ("A2").
I would like to do so using VBA macro, despite the fact that I could simply select the range and use Ctrl+H with Find Text and Replace Text. Many thanks.

Hi, i'm looking for macro that would find text select that row and define a name for that row, for example find text "hello" select that row and define the name for that row "hello". i got to the point where it finds the name and selects that row - how can i define the selected row?

thank you

here is the code i got so far

Application.CommandBars("Stop Recording").Visible = False
Cells.Find(What:="hello", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).EntireRow.Select

Hi all,

I am looking for a formula ( or 2 formulas) to copy some characters and then erase from original...

basicall i have an excel sheet that have a name of a product and also the manufacturer on the same field and i need them seperating eg:

a1 (name)
(Classic) Soft Protection Dog Collar 14 x 5/8inch (Red)

and needs to be:

a1 (name)
Soft Protection Dog Collar 14 x 5/8inch (Red)

a2 (manufacturer)
Classic

i have around 2k lines to do (including the descriptions which have the manufacture in at the begining in the same format so i am am after a formula that will copy the manufacturer name to another cell and remove it from the original including the '( )'

I think it will have to be 2 x formulas to get the result i need but i am completly stumped on how to do it.

any help/advise etc would be greatly appreciated.

thanks in advance - Mark

Hi All,

I have a big excel file, what I want is to find the dup values and remove the duplicate records as well.

I am attching an excel sheet to simulate the problem.

hare krishna
Alok

Hi,

I've got 400 lines of data similar to

5. Sisters of War (2010) (TV) ... Distributor (2011) (Australia) (***)
6. My Soul to Take (2010) ... Distributor (2011) (Australia) (***)
7. The Ward (2010) ... Distributor (2011) (Australia) (***)
8. Fubar II (2010) ... Distributor (2010) (Australia) (***)

in one column in excel 2007.

I want the text between the first number and the year.

Whats the best way of doing this?

Hi,
I have a question about Excel macros function.
I am using function =FIND("text",A2,1) and it is working in Worksheet.
But, it is giving me an error in macros. Please, see my macros.

I will be so grateful for any ideas.

Thank you.

Sub find()

Dim strText As String
Start = find("text", A2, 1)

strText = Mid$(Range("A2").Text, Start, 9)
MsgBox strText

End Sub

I see that you've helped numerous people; Could you please look at my simple problem.....(It's hard for me; but a piece of cake for you I'm sure.) Thanks!

I have this problem!

I have a spreadsheet with products I need to match another column with pictures. I have about 200 products and only 140 pictures. How can I match them up and remove the ones that don't have a cooresponding picture? I've enclosed the Excel file. Thanks!

Column A - Products Column F- Picture File = If These match

If they don't match - delete them so I have one clean file with products and picture files by row. I hope that helps! Thanks!example.xls

Hello Fellow Board Members,

I'm having a little brain fart today, what I'm trying to do is find in between numbers and count them, lets say I have a row of numbers:

CELL

A1 - 399
A2 - 430
A3 - 455
A4 - 499
A5 - 592
A6 - 401
A7 - 400
A8 - 525
A9 - 345
A1 - 456

What will be the formula to find lets say the numbers between 400 - 499 and then count how many numbers is equal to and in between 400 - 499? we will put the formula in cell B1.

Thank You and HAPPY 4th of July..

I’m working on this excel file for professional purposes where I need to create a function that runs through two different columns in a Table A, and compare it with cells in a column in Table B, then returns data from another column in Table B.

If you’re starting to feel somehow dizzy, I will try to put it in a simple way.

What do we already have?

1. Table A that has a buyer_id column, and a seller_id column.
2. Table B that has a client_id column and a client_name column.
3. Table C, where the function should be inserted to return the client’s name.

What is needed to be done?

Insert a function in Table C that does the following:
1. Check the buyer_id and seller_id in table A.
2. Check client_id in Table B.
3. Find matches between client_id and the other two columns seller_id, buyer_id, if there are ones.
4. If a match is found, then return the corresponding client_name from Table B.
5. Else, return FALSE.

I have came up with a certain formula of “ifs” and “ors”, but the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

Is there anyone in here who can lend me a hand in this please?

Thanks in advance.

I think this is really easy to do.... I need a macro that looks in column F for a cell with "TEXT" in it. When it finds it, it selects it and centers the screen on it. Then if I push the button I am going to assign to this macro again, it continues from where it is now and finds the next cell in the same colulmn F that has "TEXT" in it. So for example the first time I hit it, it might find "TEXT" in F456 and center on it, then I hit it again and it finds it in F512 and centers on that, etc, etc. Can someone help me with this?

I have pretty complex model that I inherited from another banker. Customer is complaining that when cell in Assumptions Sheet is changed, it freezes their computer and runs very very slow.

I want to simplify the model by finding relationship between Assumptions and Data Tables. When I change cell in Assumptions then during the calculation excel send message: Calculate Data Table 1, 2, ... 5.

My question is how I can find what are physically Data Table 1, Data Table 2, .. 5 for change of specific cell, so I can find if any Data Table is unnecessary in order to simplify the model.

Best Regards,

Ned

Hello,

I have tried to look around and see if I could find anything on this, but haven't so far (maybe I'm just not searching using the right terms). I am trying to add code to my macro that will find a row with the term "Model#" in it, and then select the text from ~ to ~ and paste it at the end of the cell containing the Phrase "Custom Parts". These cells will ALWAYS be the same spacing from each other. Heres an example:

This is what I'm looking at.. I am trying to get the "GH82347" copied onto the line that says "Custom Parts : ". I am trying to paste it into the same cell but at the end. These repeat anywhere between once to 100 more times so I can't base it on the row number. I've tried recording a macro, but yeah wow.. that DIDN'T work very well.. even for a starting point. It just copied the lines (I tested it by removing a letter from the middle section and ran the macro. The letter was back after I ran the Macro). The end result I am looking for is below as well.

What I'm Looking At:

Line# 101
Custom PO :
Custom Parts :
Case :
Brand :
Case Begin :
Case End :
Model# : GT537256~GH82347~8348389

Desired End Result:

Line# 101
Custom PO :
Custom Parts : GH82347
Case :
Brand :
Case Begin :
Case End :
Model# : GT537256~GH82347~8348389

Hi all.
I've got a bank statement listing of cleared checks. But two columns of checks with the check number, date and amount all show up in one cell. for example, cell A1 is:

90341* Aug 22 100.00 91512* Aug 06 456.37

I'd like to take 8 columns and have the first column show what is before the first space (the first check# - 90341*, in this case), then the second column show what is between the first and 2nd spaces, etc.

I've got the '90341*' and "Aug 22" seperated into two columns using a =Find("Aug") function and pulling the data a certain number of characters to the left or right of the text place returned by the Find(Aug) function. But I run into problems when the dollar amounts are varying character lengths. So it just dawned on me that the geniuses at this board could probably help me just show what is between the spaces in seperate columns.

thanks.

how do i remove hyphens from between numbers witout having to edit manually I
have a column of 3000 numbers similar to below.

0-7643-2200-1 becomes 764322001

0-7643-2299-X becomes 076432299X

if i format the column as text and then use the search and replace dialog
box Ctrl-H search for hyphen and replace with nothing it works on numbers
which end in a letter ie -X but will remove the 0 at the beginning of numbers
not ending in a letter ie -1 in my example

Can anybody help
Thanks Ian

I have several columns of addresses in varying formats. I want to find and remove all data to the RIGHT of the first comma in the string of text. Any suggestions on ways to do this? I've tried Excel's Find/Replace, but couldn't find comma nor delete all charactors following the comma.

I also tried some formulas like:
=MID(SUBSTITUTE(A2," ",""), FIND(",",SUBSTITUTE(A2," ",""))+1,2)

but that didn't work well.

The following are two examples of cells in a column. I want to strip out all the information right of the comma.

Sacramento, CA 95815-3514
Los Angeles, CA 90025

Thank you for your ideas and suggestions.

Hello,

I was after a custom UDF function that deletes all spaces found in a cell containing text... all spaces means:
- single spaces between characters
- double, triple etc.. spaces between characters
- spaces found in the beginning and end of text

could someone help with this?

thanks
andy

I have the following string of text and want to take out certain parts of it. Assume the string is in cell A1.

In cell B1 I want "Company ABC DEF"
In cell C1 I want "10.50%"
In cell D1 I want "12/13/2014"

String:
Company ABC DEF (USD) 10.50% 12/13/2014

I appreciate the help. Also, can someone give me a brief description of how the MID formulas work (as a bonus)? I used =MID(A1,FIND(")",A1,1)+2,45) and it returns "10.50% 12/13/2014".

So I see that that formula says start from 1 character after ")", count 2 over, and then put in the next 45 characters. How do I do an in between amount, and how do I just have it come from the LEFT but stop when it finds " ("?

Other that use find and replace to remove hyphens in a sheet is there
another way to remove hyphens in only certain parts of a string. Here are
some examples of what i want to do:

before
Acalypha hispida 'Alba'
after
Acalypha hispida Alba

before
Acanthus mollis 'Hollard's Gold'
after
Acanthus mollis Hollard's Gold

before
'Acer campestre Postelense'
after
Acer campestre Postelense

As you can see there are several options. Can a formula take all these
criteria's into account, particularly the second example where a hyphen
needs to be retained.

Pat

Hello all. Please see the attached file. I regularly receive input files which I have to reformat and upload to a website as a .csv. The website does not like the / between the characters on the Input Data tab. Is there a way to automatically search and remove them and leave the rest of the characters intact? The columns do not always appear in the same order and there could be more or less columns. On the Import Template tab, columns A-I will always be there, so ideally the code should make the changes from the Input Data tab and copy the present columns (J thru whatever) header and data over to the Import Template tab starting in 'J1'. I think that VBA would be the best way to go due to the variable nature of the columns. Any ideas how to accomplish this?

Thanks in advance,
Andrew

is there a way to automate =left(b1,40) and remove commas from the text?

text in the cell would be something like a name for instance john doe, LLC but some names are to long so was using the =left to reduce it to 40 characters and using find/replace for the commas replacing it with a space.

Hi there,

I would like to do the following.

I have a cell which shows `78945~

I would like a formula which removes the symbols, so as to show 78945

The number of numbers may change, so its important that this can be variable.

I think the formula would work best if it takes every character between the first character and the last character

If this is not possible, then just between the actuall symbol, however this is not ideal, if the characters happen to change. However for now, I think the characters wil always be ` and ~. The variable will be how many numbers.

Many thanks

Simon

I'm having trouble with a script i wrote which copies a value from one place to another. The problem is that when it copies, it copies a wierd character which is shown by a square with a questionmark inside followed by the correct text, like so:
"?results"
What i want to do is remove that character and leave make the cell only have "results" in it.
Anyone know how i can do this?
Thanks a lot.


No luck finding an answer? You could always try Google.