Find text, Remove it/Replace it then add it to end of current cell orin between (depends) = examples

  • Thread starter Thread starter ezduzitez
  • Start date Start date
E

ezduzitez

Hello all,

I would like to be able to find and change cells as noted on the examples below. Your help is greatly appreciated.

If I get help with one example I might be able to figure out the rest.

Ex-1
(Original data) Diameter - Basic: = 2.800 in
(Desired data ) Ø2.800 BASIC

Ex-2
(Original data) Angle Dimension - Basic: = 4.72 deg
(Desired data ) 4.72° BASIC

Ex-3
(Original data) Angle Dimension - Basic: = 51.4 deg 6 places
(Desired data ) 51.4° BASIC - 6 PLACES

Ex-4
(Original data) Linear Dimension - Basic: = .660 in 3 places
(Desired data ) .660 BASIC - 3 PLACES

Thanks you,

EZ
 
Hi,

Am Wed, 1 May 2013 12:32:10 -0700 (PDT) schrieb (e-mail address removed):
Ex-1
(Original data) Diameter - Basic: = 2.800 in
(Desired data ) Ø2.800 BASIC

Ex-2
(Original data) Angle Dimension - Basic: = 4.72 deg
(Desired data ) 4.72° BASIC

Ex-3
(Original data) Angle Dimension - Basic: = 51.4 deg 6 places
(Desired data ) 51.4° BASIC - 6 PLACES

Ex-4
(Original data) Linear Dimension - Basic: = .660 in 3 places
(Desired data ) .660 BASIC - 3 PLACES
Click to expand...

your string in A1, then in B1:
=IF(ISNUMBER(FIND("places",A1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Diameter","Ø")," deg","°")," in",)," - Basic: =",),"Angle Dimension ",),"Linear Dimension ",),"places","PLACES")," "," BASIC - ",1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Diameter","Ø")," deg","°")," in",)," - Basic: =",),"Angle Dimension ",),"Linear Dimension ",),"places","PLACES")&" BASIC")


Regards
Claus Busch
 
Hi,

Am Thu, 2 May 2013 10:13:22 +0200 schrieb Claus Busch:
=IF(ISNUMBER(FIND("places",A1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Diameter","Ø")," deg","°")," in",)," - Basic: =",),"Angle Dimension ",),"Linear Dimension ",),"places","PLACES")," "," BASIC - ",1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Diameter","Ø")," deg","°")," in",)," - Basic: =",),"Angle Dimension ",),"Linear Dimension ",),"places","PLACES")&" BASIC")
Click to expand...

or a little bit shorter:
=UPPER(IF(ISNUMBER(FIND("places",A1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("Diameter",A1)),"Ø"&RIGHT(A1,LEN(A1)-FIND("=",A1)-1),RIGHT(A1,LEN(A1)-FIND("=",A1)-1))," in",)," deg","°")," "," BASIC - ",1),SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("Diameter",A1)),"Ø"&RIGHT(A1,LEN(A1)-FIND("=",A1)-1),RIGHT(A1,LEN(A1)-FIND("=",A1)-1))," in",)," deg","°")&" BASIC"))


Regards
Claus Busch
 
Hi,

Am Thu, 2 May 2013 17:37:33 +0200 schrieb Claus Busch:
=UPPER(IF(ISNUMBER(FIND("places",A1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("Diameter",A1)),"Ø"&RIGHT(A1,LEN(A1)-FIND("=",A1)-1),RIGHT(A1,LEN(A1)-FIND("=",A1)-1))," in",)," deg","°")," "," BASIC - ",1),SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("Diameter",A1)),"Ø"&RIGHT(A1,LEN(A1)-FIND("=",A1)-1),RIGHT(A1,LEN(A1)-FIND("=",A1)-1))," in",)," deg","°")&" BASIC"))
Click to expand...

again a little bit shorter:
=UPPER(IF(ISNUMBER(FIND("places",A1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("Diameter",A1)),"Ø","")&RIGHT(A1,LEN(A1)-FIND("=",A1)-1)," in",)," deg","°")," "," basic - ",1),SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("Diameter",A1)),"Ø","")&RIGHT(A1,LEN(A1)-FIND("=",A1)-1)," in",)," deg","°")&" basic"))


Regards
Claus Busch
 
Hi Claus,

I've tried to incorporate this into my module without success. I know enough to get by, but this is too advance for me. I get this "Compile error: Expected: line number or label or statement or end of statement"

Would you mind writing it as if it was one Module for me. I'm missing a basic step and just can't get it :(

Sub Basics()

(needed info)

End Sub

Thanks again,

EZ
 
Hi EZ,

Am Fri, 3 May 2013 07:24:53 -0700 (PDT) schrieb (e-mail address removed):
Would you mind writing it as if it was one Module for me. I'm missing a basic step and just can't get it :(
Click to expand...

try:
Sub ChangeText()
Dim LRow As Long
Dim rngC As Range
Dim myStr As String
Dim Start As Integer

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For Each rngC In Range("A1:A" & LRow)
myStr = IIf(InStr(rngC, "Diameter"), _
"Ø" & Right(rngC, Len(rngC) - InStr(rngC, "=") - 1), _
Right(rngC, Len(rngC) - InStr(rngC, "=") - 1))
myStr = Replace(Replace(myStr, " in", ""), " deg", "°")
Start = InStr(myStr, " ")
If Start = 0 Then
myStr = myStr & " basic"
Else
myStr = Left(myStr, Start - 1) & " basic - " & _
Right(myStr, Len(myStr) - Start)
End If
myStr = UCase(myStr)
rngC = myStr
Next
Application.ScreenUpdating = True
End Sub


Regards
Claus Busch
 
Hi Claus,

You have the right idea, but when I execute it for some reason it fixes allthe cells in the file :(

Hi Ron,

This is my first time using UDF and did not work for me. Don't know what I missed. I copied and pasted the data provided into a module, then I added =ReArrangeText(D8)into a cell, then I called it in a function and it asked me to select a cell or range so when I selected cell D8 nothing happened and when I selected a range from D5 to D12 nothing either :(

I'm providing additional information and hope it will help me explain my issue better. The data that needs fixing is on Column D only and starts at Row 5 and goes down from there.

Original sample data from D5 thru D12
(D5)Ø.938 +.010/-.001 - 7 places
(D6)¨.39 +/-.03
(D7)|⌖|⌀0.030|A|B|
(D8)Diameter - Basic: = 2.800
(D9)Ø.266 +.006/-.001
(D10)Angle Dimension - Basic: = 4.72°
(D11)Angle Dimension - Basic: = 5.2° - 3 Places
(D12)Linear Dimension - Basic: = .660 - 3 Places
==================================================
Desired sample data from D5 thru D12
(D5)Ø.938 +.010/-.001 - 7 places
(D6)¨.39 +/-.03
(D7)|⌖|⌀0.030|A|B|
(D8)Ø2.800 BASIC
(D9)Ø.266 +.006/-.001
(D10)4.72° BASIC
(D11)5.2° BASIC - 3 Places
(D12).660 BASIC - 3 Places

Thanks for your help
 
Hi,

Am Wed, 15 May 2013 14:14:23 -0700 (PDT) schrieb (e-mail address removed):
You have the right idea, but when I execute it for some reason it fixes all the cells in the file :(
Click to expand...

then try:

Sub ChangeText()
Dim LRow As Long
Dim rngC As Range
Dim myStr As String
Dim Start As Integer

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For Each rngC In Range("A1:A" & LRow)
If InStr(rngC, "=") Then
myStr = IIf(InStr(rngC, "Diameter"), _
"Ø" & Right(rngC, Len(rngC) - InStr(rngC, "=") - 1), _
Right(rngC, Len(rngC) - InStr(rngC, "=") - 1))
myStr = Replace(Replace(myStr, " in", ""), " deg", "°")
Start = InStr(myStr, " ")
If Start = 0 Then
myStr = myStr & " basic"
Else
myStr = Left(myStr, Start - 1) & " basic - " & _
Right(myStr, Len(myStr) - Start)
End If
myStr = UCase(myStr)
rngC = myStr
End If
Next
Application.ScreenUpdating = True
End Sub


Regards
Claus Busch
 
Thank you Claus and Ron,

This last Macro did the job :)

Your help is greatly appreciated.

Sincerely,

EZ
 
Back
Top