Almost always. I just can't figure out what the fuck you're trying to doIs there a way to do this other than manually?

- Thread starter Fingolfin
- Start date

Almost always. I just can't figure out what the fuck you're trying to doIs there a way to do this other than manually?

Create a court exhibit that lists claim numbers and values

Excel isn’t Word. It doesn’t make “pages” like that. Just change your print layout instead of moving the columns.I have 26,000 unique numbers in column A. Each number has a corresponding dollar figure in column b.

I am trying to format this into three columns of the number dollar combination for printing (so 6 columns of data). I would like the first page of the printed three column sheet to contain the highest dollar figure unique number combinations. Essentially, this means col A rows 1-50 are the biggest numbers, col C rows 1-50 are the next biggest, col E rows 1-50 the next, then shift back to col A rows 51-100 the next biggest because it starts the second printed page.

Is there a way to do this other than manually?

Or paste it into Word and switch the page layout to 3 columns (for printing purposes). This will "snake" the data as sjstraub described.Excel isn’t Word. It doesn’t make “pages” like that. Just change your print layout instead of moving the columns.

I have a column with text separated by colons

Abcdef: ab: abc: abcd

I just want the first 2 sections

Abcdef: ab

Is there a good way to do this in a formula, I dont want to do text to columns and the squish them back together. I need it to be sustainable.

A clever combo of left, right, search will get you there. Give me a few and I'll hammer it out

I have a column with text separated by colons

Abcdef: ab: abc: abcd

I just want the first 2 sections

Abcdef: ab

Is there a good way to do this in a formula, I dont want to do text to columns and the squish them back together. I need it to be sustainable.

You are the wind beneath my wings today!A clever combo of left, right, search will get you there. Give me a few and I'll hammer it out

:*You are the wind beneath my wings today!

=LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1)A clever combo of left, right, search will get you there. Give me a few and I'll hammer it out

If you want to drop the colon I could get you there. Just let me know, boo

HahahaIf you want to drop the colon I could get you there.

=LEFT(A2,SEARCH(": ",A2,1)-1)&" "&RIGHT(LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1),LEN(LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1))-(SEARCH(": ",LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1)))-1)

Returns: abcdef ab

- Joined
- May 2, 2015

- Messages
- 82

Concatenate if desired on the back end to add the colon back in

Lol, yea I'll keep the colon. Luv u!Hahaha

=LEFT(A2,SEARCH(": ",A2,1)-1)&" "&RIGHT(LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1),LEN(LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1))-(SEARCH(": ",LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1)))-1)

Returns: abcdef ab

It could, but some strings have many colons, so i didn't want a bunch of loose columns floating around, and it'd be a pain to do on the regular

Concatenate if desired on the back end to add the colon back in

Mother trucker.=LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1)

If you want to drop the colon I could get you there. Just let me know, boo

Is there a way to adapt this in the event a text string only has 1 colon?

Add an If around the second argument?

That assumes a static dataset that needs to be handled once. By using a formula it allows an algorithmic solution that can be applied automatically.

Concatenate if desired on the back end to add the colon back in

Text to columns function is pleb

Yeah if there's only 1 column do you just want the full string returned?Mother trucker.

Is there a way to adapt this in the event a text string only has 1 colon?

Add an If around the second argument?

You got itYeah if there's only 1 column do you just want the full string returned?

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,":",""))>1,You got it

LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1),

A2)

Woah.keanu.csv=IF(LEN(A2)-LEN(SUBSTITUTE(A2,":",""))>1,

LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1),

A2)

Plugged this in and noticed one correction the logical test needs to be >2, not >1. For the strings where there is one colon the logical test equals 2=IF(LEN(A2)-LEN(SUBSTITUTE(A2,":",""))>1,

LEFT(A2,SEARCH(": ",A2,SEARCH(": ",A2)+2)-1),

A2)

Great formula dude!

2