Free Microsoft Excel 2013 Quick Reference

Remove duplicate text from a string in a row of cells

I got my self into trouble again

I have a large worksheet and I managed to duplicate data in a row of cells.
MOT 1/F1,
B/P 1f2,
B/P 1f2 In the string above I need to delete all text from the first comma to the end of the string so the above would look like:
MOT 1/F1 This is an example in one cell of many. The text prior to the first comma may or may not repeat and it is the only portion of the text I wish to keep. The first comma is not always in the same place (character count position).

Any hints, tips or examples that will prevent further carpel tunnel issues is appreciated.


Post your answer or comment

comments powered by Disqus
How we can delete the duplicate word from a string from a particular cell in
EXCEL

hello again

Is there a quick way to remove all punctuation from a string in a cell? (this ties in with my frequency analysis program from earlier)

Thanks
Hazza

Hi,
I would like to extract a text from a string. I found this post and it did what I needed however some of my cells have two values that need to be extracted example:
A1
http://www.excelforum.com/newthread.php?do=its+hot&ip=123.1.865&date=1208http://www.excelforum.com/newthread.php?do=its+hot&ip=133.1.965&date=1208
A2 http://www.excelforum.com/newthread.php?do=excel+rocks+my+life&server=powerful&ip=246.86.66
A3
http://www.excelforum.com/newthread.php?do=i+like+food&j=dasdas&ip=123.1.865&k=sadaasdhttp://www.excelforum.com/newthread.php?do=i+like+food&j=dasdas&ip=133.1.666&k=sadaasd
A4 http://www.excelforum.com/newthread.php?do=nice&ip=111.111.11&f=asdsad

so I would like to get
B1 123.1.865, 133.1.965
B2 246.86.66
B3 123.1.865, 133.1.666
B4 111.111.11
Any idea how to do so? Thanks

Hey everyone,

I have a long list of around 400 phrases, all in their individual cells. My problem is some of the terms have duplicate words in them.

Here's an example: http://i51.tinypic.com/1ttpg5.jpg

Is there any way to remove this duplicate text from the cell?

Thanks,
Russell

Hi,

I need a formula that could help me remove 2nd, 3rd or 4th word from a string of text. Suppose the text is "DRBS 88V XRD Database Management System"

I want a formula that could remove 88V from the cell and keep the rest: "DRBS XRD Database Management System"

Similarly the formula should work to remove 3rd word XRD to keep the rest: "DRBS XRD Database Management System"

I am not very fluent with Excel, so need your help.

Thanks

Hello,

My latest problems involves a large string of characters we will call it DistList it contains sets of characters seperated by ";" This String is actually an e-mail distribution list. It uses code similar to the below to concatenate the headers:

If Sheets("US Retail Outages").OLEObjects("CheckBox1").Object.Value = True Then DistList = DistList & Header1
If Sheets("US Retail Outages").OLEObjects("CheckBox2").Object.Value = True Then DistList = DistList & Header2
If Sheets("US Retail Outages").OLEObjects("CheckBox3").Object.Value = True Then DistList = DistList & Header3
If Sheets("US Retail Outages").OLEObjects("CheckBox4").Object.Value = True Then DistList = DistList & Header4

so on and so forth

before DistList gets inserted in the ".To" field in outlook I would like to check to make sure I do not have Duplicate e-mails. People hate to get spammed with extra mail

I've done a couple searches for some code that might help, but they all deal with addresses that are seperated in cells then comparing the cells

I however have a string which complicates things a bit.

Any suggestions would be quite appreciated.

Thanks again in Advance,

Dana Oliva

Using VBA, what is the simplest way to remove the text from a cell that contains both text and a number. For example:

Cell B8 contains:
tals 84,823.22

I need the letters tals removed & leave only the number in the cell:

Cell B8 needs to be:
84,832.22

I really appreciate any help. mikeburg

I would be grateful if someone could show me a way to remove the first 3 characters (including a space between them) from a string. Also, is there a way (in a seperate formula) to do the oppsote and remove all the values with the exception of these first 3 characters?

I've attached a sample calc and would be grateful for any advice on this.

Thanks

I have a range of cells that contains data in the form on strings. I need to extract a part of this string and paste it (to the empty cell on the right).

Range "AV" contains the cells with strings
I need to extract the data between "0" OR "0:" and till the end of the string in the cell OR till "1"

Can anyone show me how to do this with a Macro (VBA code) or anyway else ? Thank You

example :

(String in a cell)

shawn012k
0: james
pdf: 321
1:
pdf:
2:
pdf:

Extracted Data (pasted in the cell to the right): james
pdf: 321

I'm trying to remove duplicate entries and only display one row of data in a particular column. Here is an example of my data set.

333230081202300015333230082202300015333230081202300015333230082202300015333230083202300015333230081202300015333230081202300015333230081202300015

This is just a small piece of numerous sets of duplicate data. What I'm looking to do is copy a list of only one entry per set in column 4. I've read other posts but am unsure how to work code into my sheet. Is a macro necessary?

--------------------------------------------------------------------------------

I have a range of cells that contains data in the form on strings. I need to extract a part of this string and paste it (to the empty cell on the right).

Range "AV" contains the cells with strings
I need to extract the data between "0" OR "0:" and till the end of the string in the cell OR till "1"

Can anyone show me how to do this with a Macro (VBA code) or anyway else ? Thank You

example :

(String in a cell)

shawn012k
0: james
pdf: 321
1:
pdf:
2:
pdf:

Extracted Data (pasted in the cell to the right):
james
pdf: 321

Is there a way to automatically remove duplicated data within a column in
Excel? For example, I have a column of over 10000 rows. The cell contents
are call numbers, many of them are duplicate and I'd like to remove them
without sorting and manually removing them.

What is the command/function that could match "All" of the text in cell A (in Specific Order) to "Some" of the text in Cell B and delete the duplicate text phrases that appear in "B"?

Example
A-------------------------B--------------------------------------------C
the quick brown fox------The quick brown fox jumped over lazy dog----over lazy dog
red fox------------------ Red Fox jumped over lazy dog---------------over lazy dog
Gray & Brown Fox ------- Grey & Brown Fox over lazy dog -------------over lazy dog
Blue & Red Fox 23 ------- The Blue & Red Fox is over lazy dog--------- The over lazy dog
Green & Brown Fox 45-----The Green & Brown Fox 45 over lazy dog---- The over lazy dog

Desired results is C === a-b=c

Excel 2003
I am trying to use a check box to display a category (Text) from a list in a
cell and have tried code based on an "if structure" with no success. Has
anyone got any suggestions.

I would like to get the numeric value from a string in a cell that can have many unknown characters.
Examples: (Assume column A)
No. A X 4.900 = 4.900
No. A Y -.625 = .625
No. B X 15.500 =15.500
No. B Y -2.137 = 2.137
No. C X 17.592 = 17.592
No. C Y -6.451 = 6.451

More examples:
[17.342] = 17.342
[15.4°] = 15.4
[R 1.000] = 1.000
[15.287] = 15.287
[R .500] = .500
[33.7°] = 33.7

All "=" values shown are the numeric value extracted from the examples.
I will be adding and subtracting numbers from the extracted values.

Any hints, tips or examples are appreciated.

Hi there,

I am trying to extract a number of character from a string. The format of the string is as follows:

First Name (space) Last Name (space) RR (cost centre number) (space) (date)

So an example is as follows:

JOHN CITIZEN RR533 (26.02.07 TO 25.05.07)

I need to extract all characters before the RR, so basically the first and last name of the person. I would really appreciate if anyone could let me know how to do it.

I cannot use the left or right function or even the len funtions as the number of characters in the string vary. The number of characters to the right of "RR" are also non consistent unfortunately.

Any sort of help would be great!

Cheers!

Hi All,

Is there any way I can copy the text from a shape in a worksheet to a
cell on the same worksheet?
Im looking for a formula, a macro would be last resort.

Thanx in advance..

Hi there,

I have been unable to find a workable solution to this problem elsewhere. I've tried a few different things, but haven't been able to come up with a reliable method.

I want to extract two numbers from a string into two separate cells, however the length of the numbers can vary each time. The start of the string is always the same i.e. "Solution(" , and the numbers are always separated by a semi colon.

Solution(9.51; 9.1044)
Solution(0.08847; 0.06728)
Solution(5.1826; 215.02)

Say for example, Solution(9.51; 9.1044) is in A1, I want to extract the number 9.51 into B1, and 9.1044 into C1.

Any help would be much appreciated

Is there a way to automatically remove duplicate content from a column in excel?

How to combine text from many cells without writing each cell adress,
just the scope of cells?
Can I combine tex from cells A1 to A3 in a faster way than writing a
formula: '=A1&" "&A2&" "&A3' ? I need to find a way to combine text
from a really huge number of cells, like from B1 to B3489, and I want
to do it without writing every cell adress.

--
sierra
------------------------------------------------------------------------
sierra's Profile: http://www.excelforum.com/member.php...o&userid=25099
View this thread: http://www.excelforum.com/showthread...hreadid=386088

How to combine text from many cells without writing each cell adress, just the scope of cells?
Can I combine tex from cells A1 to A3 in a faster way than writing a formula: '=A1&" "&A2&" "&A3' ? I need to find a way to combine text from a really huge number of cells, like from B1 to B3489, and I want to do it without writing every cell adress.

I am trying to pull 600 contact records from a list in excel of over 50,000.
I think a random sample will work fine. There are 2 demographic markers that
I have pulled counts on for the entire database. I need to pull a similar
ratio of contacts in my sample of 600, but I believe if I pull a random
sample it will work out. Any ideas on how to do this?

Hi Guys,

I need help in writing macro for "Removing duplicates with less elements in a row."
I am trying to remove duplicate entries from a file based on the number of data present in a row. Please see below example:
Name Col-1 Col-2 Col-3 Col-4
abc 1 3 4
abc 1 2 3 4
abc
abc 1 4

I want to retain the row with maximum elements(row 2 in above eg.).
Result should be:
Name Col-1 Col-2 Col-3 Col-4
abc 1 2 3 4

Currently I am doing this manually, by adding countA at the end of each row, then arranging them in descending order. That will make sure that the row with more data comes first n hence gets retained, while other rows gets deleted.
Can this be done using Macro? Please help.
(Hope i am clear enough.)

Below macro just deletes the rows,

Public Sub DeleteDuplicateRows()

Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))
Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = Rng.Cells(R, 1).Value

If V = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
End If
Next R

EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)
End Sub

Hi all,

I have a woorkbook where I have a cell that has the following apperance:

12345678-ABCDEFG

The numbers can vary in length and as well as the text.

What I am trying to do is to split this cell into to columns.

Column 1 shows the account number, Column 2 should show the Text and the "-" sign should not be in any of the columns ie it should look something like this

Column1 Column2
12345678 ABCDEFG

I have found a useful function for the first part thanx to Mikerickson
URL:http://www.ozgrid.com/forum/showthread.php?t=68969

	VB:
	
 
    Dim i As Long 
    For i = 1 To Len(inputStr) 
        midNumber = CDbl(Val(Mid(inputStr, i))) 
        If midNumber  0 Then Exit Function 
    Next i 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But how do i seperate the text and how do Crete a macro that would loop through some 2000 rows and create a column A and B from Column A?

Thankful for any help.

Robert


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