Trim Cell contents depending on a space in sentence

[gmercer82]

I need help changing a cell formula to use in a macro.

In the column this formula is referencing it will have information like the following:

01575 Product 1
25427 Areas1/Areas2
89854787 Section 4

The formula is what I am using to check if the line has a space for character 6. If it does it will remove the first 6 characters. If the 6th character is not a space then it will remove the first 9 character. At the end of both it will add a "; "This will make the info look like

Product 1;
Areas1/Areas2;
Section 4;

Here is my formula
=IF(MID(​

​

​

​

A2;6;1)=" ";RIGHT(A2;LEN(A2)-6)&"; ";RIGHT(A2;LEN(A2)-9)&"; ")

Thank-you in advance for any help.​

 

[Rick Rothstein]

Given that structure, you can just split the text on the first space...

=MID(A1,FIND(" ",A1)+1,99)&";"

The 99 in that formula assumes your text will never be longer than 99 characters. If it can be longer, simply change the 99 to a number equal to or larger than the most number of characters your text can ever have.

 

[gmercer82]

How would I change this for running in a macro? This will run for rows 1 to 250. I think I have the code to make it run from Row 1 til there are not more rows.

 

[Rick Rothstein]

How would I change this for running in a macro? This will run for rows 1 to 250. I think I have the code to make it run from Row 1 til there are not more rows.

From inside a macro, I would probably do it this way (where you would replace "CellValue" with whatever variable or range call you are using to obtain a single cell's value)...

TheNumber = Split(CellValue) & ";"

 

[gmercer82]

I have tried and was receiving an error message and the debugger would highlight the line you provided.

Here is a list of the code I am using right now. I would like this code to run for every value in Column A and then put the result in Column B.

Dim WS As Worksheet
Dim LastRow As Long
Set WS = ActiveSheet
With WS
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For a = 1 To LastRow

* where i am putting code

Next
End With

I want this to put the information in column B.

 

[Rick Rothstein]

I have tried and was receiving an error message and the debugger would highlight the line you provided.

Here is a list of the code I am using right now. I would like this code to run for every value in Column A and then put the result in Column B.

Dim WS As Worksheet
Dim LastRow As Long
Set WS = ActiveSheet
With WS
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For a = 1 To LastRow

* where i am putting code

Next
End With

I want this to put the information in column B.

Show us what you were putting there (it should not be exactly what I posted as that was written without knowing anything about your setup).

 

[gmercer82]

This is what I am trying.

Dim WS As Worksheet
Dim LastRow As Long
Set WS = ActiveSheet
With WS
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For a = 1 To LastRow

.cells(a,2).value=[FONT=Arial, sans-serif]MID(rows.count,1[/FONT];FIND(" ",[FONT=Arial, sans-serif]rows.count,1[/FONT])+1;99)&"; "

Next
End With​