Breaking my head with this formula

[RafaelGS]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Description[/TD]
[TD]Measurement (mm)[/TD]
[/TR]
[TR]
[TD]5897652[/TD]
[TD]Erhöhungsrahmen, MEF33, 320x320x75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2145456[/TD]
[TD]text....
Größe: BxHxT = 900x1250+(800(Sockel) = ignore)x340 mm
Text...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2548578[/TD]
[TD]Only text (ignore)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3579513[/TD]
[TD]Text…
H:1710mm, B:838,5mm, T:277mm[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hello everyone, I'm struggling with this formula for a while now, I need to get just the measurements of the cells under "description", let's say B2, B3... and put it in Measurement: C2, C3,...
The problem is that there are those 4 different kinds of problems, they are not in a pattern. I've tried everything, =left, =mid, with find, with it. Someone sees a way out of it? Would really appreciate.

Thank you beforehand.

 

[Ruddles]

And what exactly do you want to appear in C1, C2 and C4? Is it 75, 340 and 277? Or something else?

 

[RafaelGS]

Exactly, could be just:
C1: 320x320x75
C2: 900x1250x340
C4: 1710x838,5x277

 

[Ruddles]

I'm not smart enough to do it in a formula - I'd do it with a User Defined Function (VBA). That will probably be easier to understand if you need to modify it later.

Try this:-

Option Explicit

Public Function get_mms(ByVal arg As String) As String

  Application.Volatile
 
  Dim iPtr As Integer
  Dim iPtr2 As Integer
  
  arg = Replace(arg, "mm, ", "mm ")
  
  iPtr = InStrRev(arg, ", ")
  If iPtr > 0 Then arg = Mid(arg, iPtr + 2)
  
  For iPtr = 1 To Len(arg)
    Select Case Mid(arg, iPtr, 1)
      Case "0" To "9", "(", ")", ","
        ' do nowt
      Case Else
        arg = Left(arg, iPtr - 1) & " " & Mid(arg, iPtr + 1)
    End Select
  Next iPtr
  
  iPtr = InStr(arg, "(")
  Do While iPtr > 0
    iPtr2 = InStrRev(arg, ")")
    arg = Left(arg, iPtr - 1) & Mid(arg, iPtr2 + 1)
    iPtr = InStr(arg, "(")
  Loop
  
  iPtr = InStr(arg, "  ")
  Do While iPtr > 0
    arg = Left(arg, iPtr - 1) & Mid(arg, iPtr + 1)
    iPtr = InStr(arg, "  ")
  Loop
  
  get_mms = Replace(Trim(arg), " ", "x")
  
End Function

 

[Ruddles]

In a copy of your workbook, press Alt-F11 to open Microsoft Visual Basic, press Ctrl-R to view the Project Explorer, then go Insert > Module. A new 'standard' module will appear under Modules, probably called Module1. Double-click the name of this new module to open it, then paste my code in.

Then in your sheet, use the new function like so: =get_mms(b2)

 

[Ruddles]

Umm... hello?

 

[Ruddles]

No?

 

[Ruddles]

Did you try this? Did it work?