extracting characters at a specified limit to seperate cells

T

tevid

I have a table of multiple parts descriptions ranging from 40
characters to 600 characters in length, in order to import this into my
data base the description cannot be longer than 75 characters, how
would i go about seperating the descriptions at the 75 character mark
and then extracting them into a new cell. Here is an example of one of
my descriptions

SAW MOTOR~KF 62.01~CAPACITY : 1.3 KW~MODE OF OPERATION: S1~MOTOR SPEED
: 3430 RPM~VOLTAGE : 460 V~FREQUENCY : 60 HZ~NOM.CURRENT : 2.85
A~PROTECTION IP 54~INSULATION CLASS F~TYPE : B3~TERMINAL BOX : 3B~BRAKE
VOLTAGE : 460 V~BRAKE TORQUE : 4 NM~BLUE-GRAY~COLOR RAL 7031~WITH
THERMAL SWITCH~WT/160 C~CSA/UL - MOTOR TYPE SIGN~K 62 S/2-G4K~PART-NO.
99324899~DOC-NO. 87160003/50


which essentially needs to turn into 4 sets of 75 char cells so that i
can import them into the comment field in the database i.e.

A1 = Saw motor ....................
B1= 330 RPM.............

etc

thanks for all your help

p.s.

i have about 5000 of these descriptions that need to be extracted (i
also have to keep the ~since this specifies the carriage return for the
print outs)
 
D

Dave O

Will this do it for you?
=MID(A1,1,75)
=MID(A1,76,75)
=MID(A1,151,75)
=MID(A1,226,75)
 
T

tevid

Dave said:
Will this do it for you?
=MID(A1,1,75)
=MID(A1,76,75)
=MID(A1,151,75)
=MID(A1,226,75)
Click to expand...

that definatively worked thanks :) i think i can survive doing it that
way, maybe someone knows a way to macro this for all the cells?
 
R

Ron Rosenfeld

I have a table of multiple parts descriptions ranging from 40
characters to 600 characters in length, in order to import this into my
data base the description cannot be longer than 75 characters, how
would i go about seperating the descriptions at the 75 character mark
and then extracting them into a new cell. Here is an example of one of
my descriptions

SAW MOTOR~KF 62.01~CAPACITY : 1.3 KW~MODE OF OPERATION: S1~MOTOR SPEED
: 3430 RPM~VOLTAGE : 460 V~FREQUENCY : 60 HZ~NOM.CURRENT : 2.85
A~PROTECTION IP 54~INSULATION CLASS F~TYPE : B3~TERMINAL BOX : 3B~BRAKE
VOLTAGE : 460 V~BRAKE TORQUE : 4 NM~BLUE-GRAY~COLOR RAL 7031~WITH
THERMAL SWITCH~WT/160 C~CSA/UL - MOTOR TYPE SIGN~K 62 S/2-G4K~PART-NO.
99324899~DOC-NO. 87160003/50


which essentially needs to turn into 4 sets of 75 char cells so that i
can import them into the comment field in the database i.e.

A1 = Saw motor ....................
B1= 330 RPM.............

etc

thanks for all your help

p.s.

i have about 5000 of these descriptions that need to be extracted (i
also have to keep the ~since this specifies the carriage return for the
print outs)
Click to expand...

If you don't care how they break, then Dave's solution should be fine.

If you want the break to be in some logical spot, e.g. after a tilde, then you
will need a different method.

Also, your example has 372 characters. 4 sets of 75 will only give you the
first 300 characters. Do you want to discard the 72 extra characters? Or put
them in a fifth (or sixth or more) cell?


--ron
 
R

Ron Rosenfeld

Ron I would like to then extract the remaining characters into another
cell
Click to expand...

And what about having the break at a "logical point" (e.g. after the tilde?).

If the location of the break is not important, you can just extend Dave's
formula, perhaps with an error check when the formula returns a null string:

e.g. =IF(LEN(MID(A1,300,75))>0,MID(A1,300,75),"")

If you want the break to be at the "tilde", then one way is using VBA to write
a User Defined Function using Regular Expressions.

For that:

<alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window.
Insert/Module and paste the code below into the window that opens.
From the menu bar of the VB Editor: Tools/References and select "Microsoft
VBScript Regular Expressions 5.5" from the list of Available References.

Then, with your string in A1, enter this formula in some cell and drag across
as far (or further) than required to return the entire string:

=remid($A$1,"[\s\S]{1,74}~",COLUMNS($A:A))

============================================================
Option Explicit

Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True) _
As Variant 'Variant as value may be string or array

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Dim i As Long 'counter

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function

======================================
--ron
 
Top