Calculation on Strings

kwebihaf

New Member
Joined
Sep 18, 2013
Messages
4
Suppose I have a string like TBU/EVE/234 and i want the next strings to be TBU/EVE/235, TBU/EVE/236,TBU/EVE/237 in different cells.

what formula or functions can i use to achieve that

Thanks in advance for your quick help.

FRED
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
kwebihaf,

Welcome to the MrExcel forum.

We can not tell what cells, rows, columns your raw data strings are in.

And, we can not what cells, rows, columns your results are in.

What cell is TBU/EVE/234 in?

And, what cells are the following in?
TBU/EVE/235
TBU/EVE/236
TBU/EVE/237
 
Upvote 0
kwebihaf,

Sample raw data:


Excel 2007
ABCD
1TBU/EVE/234
2TBU/EVE/111
3TBU/EVE/222
4EVE/TBU/1000
5
Sheet1


After the macro:


Excel 2007
ABCD
1TBU/EVE/234TBU/EVE/235TBU/EVE/236TBU/EVE/237
2TBU/EVE/111TBU/EVE/112TBU/EVE/113TBU/EVE/114
3TBU/EVE/222TBU/EVE/223TBU/EVE/224TBU/EVE/225
4EVE/TBU/1000EVE/TBU/1001EVE/TBU/1002EVE/TBU/1003
5
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub CalculateStrings()
' hiker95, 09/18/2013
' http://www.mrexcel.com/forum/excel-questions/727229-calculation-strings.html#post3578407
Dim c As Range, s, d As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  s = Split(c, "/")
  For d = 1 To 3 Step 1
    c.Offset(, d) = s(0) & "/" & s(1) & "/" & s(2) + d
  Next d
Next c
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CalculateStrings macro.
 
Upvote 0
kwebihaf,

Sample raw data:

Excel 2007
ABCD
TBU/EVE/234
TBU/EVE/111
TBU/EVE/222
EVE/TBU/1000

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1



After the macro:

Excel 2007
ABCD
TBU/EVE/234TBU/EVE/235TBU/EVE/236TBU/EVE/237
TBU/EVE/111TBU/EVE/112TBU/EVE/113TBU/EVE/114
TBU/EVE/222TBU/EVE/223TBU/EVE/224TBU/EVE/225
EVE/TBU/1000EVE/TBU/1001EVE/TBU/1002EVE/TBU/1003

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub CalculateStrings()
' hiker95, 09/18/2013
' http://www.mrexcel.com/forum/excel-questions/727229-calculation-strings.html#post3578407
Dim c As Range, s, d As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  s = Split(c, "/")
  For d = 1 To 3 Step 1
    c.Offset(, d) = s(0) & "/" & s(1) & "/" & s(2) + d
  Next d
Next c
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CalculateStrings macro.

Thanks for the Macro Code, hiker95.

Exactly that is how I wanted the results to be.

But is there any Excel function/formula that i can use rather than the Macro code?

Regards,

FRED
 
Upvote 0
kwebihaf,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


You are very welcome. Glad I could help.

Thanks for the feedback.


But is there any Excel function/formula that i can use rather than the Macro code?

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
Exactly that is how I wanted the results to be.

But is there any Excel function/formula that i can use rather than the Macro code?
Assuming your data starts in cell A2, this formula placed in B1 and copied down to the last row with data, and then all those cells copied across to the last column you want to fill, seems to work...

=LEFT(A1,FIND("/",A1,FIND("/",A1)+1)-1)&"/"&MID(A1,FIND("/",A1&"//",FIND("/",A1&"/")+1)+1,99)+1
 
Upvote 0
Thanks Rick.

That formula helps and has worked.

Another thing, what if I want the results down the columns, say from Cell B1 to Cell B100.

How do I go about it?

Regards,

FRED
 
Upvote 0
Thanks Rick.

That formula helps and has worked.

Another thing, what if I want the results down the columns, say from Cell B1 to Cell B100.

How do I go about it?

Regards,

FRED
Hi Fred

Perhaps;


Excel 2010
ABCD
1TBU/EVE/234TBU/EVE/111TBU/EVE/222EVE/TBU/1000
2TBU/EVE/235TBU/EVE/112TBU/EVE/223EVE/TBU/1001
3TBU/EVE/236TBU/EVE/113TBU/EVE/224EVE/TBU/1002
4TBU/EVE/237TBU/EVE/114TBU/EVE/225EVE/TBU/1003
5TBU/EVE/238TBU/EVE/115TBU/EVE/226EVE/TBU/1004
Sheet2
Cell Formulas
RangeFormula
A2=SUBSTITUTE(A1,LOOKUP(10^308,--RIGHT(A1,{1;2;3;4;5;6})),"")&LOOKUP(10^308,--RIGHT(A1,{1;2;3;4;5;6}))+1
 
Upvote 0
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=SUBSTITUTE(A1,LOOKUP(10^308,--RIGHT(A1,{1;2;3;4;5;6})),"")&LOOKUP(10^308,--RIGHT(A1,{1;2;3;4;5;6}))+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks.

It Works also on Excel 2007.

I am grateful for the quick help.

Regards,

FRED
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top